September 21, 2005 at 1:13 pm
All queries for a particular table seems to be slow. It has one clustered index on the primary key column which of data type INT and has identity insert ON. This table has < 10000 rows and is fast with response in all other circumstances. The clustered index is at a fill factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of fragmentation which didn't look very serious. The BOL says it is not reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest that there were 72 pages and 72 pages were moved and 0 deleted. Still no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what is happening here?
All help is welcome and appreciated...
Thanks
JackofAllMasterofNone
September 21, 2005 at 1:31 pm
Do you have the before and after execution plans?
Did you try to simply update the stats (maybe with a deeper scan).
September 21, 2005 at 1:41 pm
I will try to post it as soon as I get the problem again. And also I will try to just update the stats although I am sure its not the stats beacuse I hev set the autoupdate statistics to yes and I did not see any changes for the indexes starting with _WASYS_ which are supposed to be stats..
A
September 21, 2005 at 1:45 pm
What changes did you look at to affirm that??
Also yes those are the stats I'm reffering to.
September 21, 2005 at 1:57 pm
There were no changes to the pages moved when I ran DBCC INDEXDEFRAG
September 21, 2005 at 1:58 pm
How does that [not] affect the statistics??
September 21, 2005 at 2:02 pm
I don't know I am trying to make an [un]intelligent guess no offence to you... I will get back with more info when I encounter the problem. This is a ongoing saga and I am not able to recreate the problem. It just happens....
September 21, 2005 at 2:13 pm
Start with this part >>
Do you have the before and after execution plans?
It could become usefull.
September 22, 2005 at 2:09 am
ok - so youve got a c/index on a pk that is an integer - thats great except - is that the field you are using when retriveing data - i suspect not if its a counter - try adding some useful indices.
September 22, 2005 at 12:23 pm
One point not mentioned in relation to UPDATE STATISTICS. When you execute this command on a table you then need to execute the following code:
exec sp_recompile table_name --> the table_name is the one the stats were updated for
This allows the optimizer to taker advantage of updated key/index distribution statistics for all stored procedures that use this table (a plan recompilation on the next execution).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply