Hi gurus I have run dbccshowcontig command on my database the scan density for most of them is DBCC SHOWCONTIG scanning 'ClientMenuDtl' table... Table: 'ClientMenuDtl' (1781581385); index ID: 1, database ID: 33 TABLE level scan performed. - Pages Scanned................................: 21 - Extents Scanned..............................: 5 - Extent Switches..............................: 13 - Avg. Pages per Extent........................: 4.2 - Scan Density [Best Count:Actual Count].......: 21.43% [3:14] - Logical Scan Fragmentation ..................: 38.10% - Extent Scan Fragmentation ...................: 40.00% - Avg. Bytes Free per Page.....................: 1745.9 - Avg. Page Density (full).....................: 78.43% DBCC execution completed. If DBCC printed error messages, contact your system administrator. is below 100% what does this mean by actual scan density and how can we improve it what are the different reasons that it goes bad. Can you guys please help me. I will be thankful.
That's means you have index fragmentation. (for index ID 1). You have to run DBCC REINDEX in that table. Do it in non working hours to avoid locks. Also you can find severals articles about store procedures to reindex only when is necessary. I recommend you one from Tom Pullen.
