November 29, 2007 at 1:05 pm
Little confused here. In analzing why a particular query was taking so long, a consultant reviewed and found that 92% of the cost was spent on a particular index. When looking at dbcc showcontig, I get scandensity values at 93 and logical frag at 0.4 for this index. When you run the dbcc show_statistics, the results are below. I was told to 'fix' the index. I was told the density was bad. As you can see, the stats were updated this morning. What more can I do? Sorry about the formatting.
Statistics for INDEX 'pkclaim'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- -------------------- -------------------- ------ ------------------------ ------------------------
Nov 29 2007 7:48AM 8175760 92419 200 1.2231278E-7 15.0
(1 row(s) affected)
All density Average Length Columns
-------------------------------------------------------
1.2231278E-7 15.0 claim
------------------------ ------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.2231278E-7 15.0 claimid
November 30, 2007 at 2:08 am
From what you wrote I would say there's nothing wrong with the index. Fragmentation is well within the acceptable range. Maybe it's just not the best index to use ?
Can you tell us a bit more about the query. what are you doing, how many records are effected, what's the ddl.
[font="Verdana"]Markus Bohse[/font]
November 30, 2007 at 2:42 am
check with dbcc index defrag for the same index. After use dbcc showcontig even the problem still persists if the table is not too big drop the index and recreate.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply