Reindexing a table with scan density 99%

Question

Reindexing a table with scan density 99%

Low Rider

SSCommitted

Points: 1502
More actions
June 11, 2009 at 11:33 pm

#205456

Hello, I have table called JOB table in my database.I have a index on the same.Now i have a job scheculed which reindexes the table at 4:00 AM in the morning.When i arrive at office i run a particular query which uses the above table and find the qry doesn;t seem to run at all.
AFter examining the I/O in sp_who2 i see that the CPU is ticking but the IO remains the same
The dbcc showcontig for the table at that time is as below
DBCC SHOWCONTIG scanning 'JOB' table...
Table: 'JOB' (688838362); index ID: 49, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 3489
- Extents Scanned..............................: 440
- Extent Switches..............................: 439
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.32% [437:440]
- Logical Scan Fragmentation ..................: 0.40%
- Extent Scan Fragmentation ...................: 42.05%
- Avg. Bytes Free per Page.....................: 784.0
- Avg. Page Density (full).....................: 90.31%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I ran a reindex again on the same and got the show contig as below
DBCC SHOWCONTIG scanning 'JOB' table...
Table: 'JOB' (688838362); index ID: 49, database ID: 10
LEAF level scan performed.
- Pages Scanned................................: 3488
- Extents Scanned..............................: 438
- Extent Switches..............................: 437
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.54% [436:438]
- Logical Scan Fragmentation ..................: 0.09%
- Extent Scan Fragmentation ...................: 21.46%
- Avg. Bytes Free per Page.....................: 719.9
- Avg. Page Density (full).....................: 91.11%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After running the reindexing on the table which already been reindexed just a few hours ago,i find the qry which was not doing anything start to work.
Any ideas why this could be happening
I have a SQL 2006 64 bit ent editio SP3 installed.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Hard to know for sure, but hazarding a guess, your index was defragged, but the statistics were out of date, so rebuilding the index updated the statistics which fixed the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Low Rider SSCommitted Points: 1502 More actions · Answer 2

Hi,

I have a schecdule job which reindexes the table every 4:00 Am and for now have scheduled it for every 8:00 AM,but still the same result.I am unable to run a query which simply runs immdiately after the the table and the paticular index is reindexed.

thanks

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

Is it a SP or a query :

do some testingon the query .Run it a few times and note the time.

if the 2nd or 3rd run does not run quickly , then either the plan is not being reused or optimizer is not able to find the right plan due the the run time parameter .in that case run the SP/Query with recompile option .But i have not seen this kind of behaviour since SQL Server 2000 SP4 or SQL Server 2005 SP1 ...but cant bet on this .Please laso get the query plan which will give you a much better picture .

Can you share query code and table structure .Does the selectivity /Cardinality of the table changes frequently ??..

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Low Rider (6/14/2009)
Hi,
I have a schecdule job which reindexes the table every 4:00 Am and for now have scheduled it for every 8:00 AM,but still the same result.I am unable to run a query which simply runs immdiately after the the table and the paticular index is reindexed.
thanks

If you can post the execution plan and some of the structure, it might be more helpful.

Just as an experiment, the next time the query won't run, instead of messing around with the index, try simply recompiling the query. If that doesn't work, try updating the statistics on the table(s) used by the query and do this with a full scan of the statistics.

Also, if any of this works, you should get a before & after execution plan so that you can understand why the query is working well in some instances and not in others.

I'm assuming that the same parameters are being used for each of the tests?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning