Challenge to all DBAs

I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.

Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the "Where…" clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.

My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.

The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = "Anna"; LastName = "Williams" returns result in 0.01 sec. Searching for FirstName "Benjamin"; LastName = "Watson" returns in 5 minutes.

Any kind of help is welcome and will be highly appreciated.

- Santy

san.rely@.gmail.com
Note: Edited to fix white on white font.

Run the queries in query analyzer, have it show the execution plan, and look and see what is different, if anything.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.