Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  low scan density

Author  Topic  creane Starting Member 11 Posts Posted - 2003-09-02 : 12:19:31 Hi, sometimes a covering index may not be used for certain values in an indexed column e.g. item=4 may use the index(covering non clustered) but item=3 may just perform a clustered index scan.. My question is basically after looking at the dbcc showcontig i see the following output some of the scan densities are very low- 13%?, would this be a source of why sometimes the index is used and othertimes it isnt for the same query.. Similarly if I use item<4 the index is not used either..any help greatly appreciated DBCC SHOWCONTIG scanning 'mytable' table... Table: 'mytable' (1143675122); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 22968 - Extents Scanned..............................: 2896 - Extent Switches..............................: 20544 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 13.97% [2871:20545] - Logical Scan Fragmentation ..................: 44.02% - Extent Scan Fragmentation ...................: 60.08% - Avg. Bytes Free per Page.....................: 3203.6 - Avg. Page Density (full).....................: 60.42% DBCC SHOWCONTIG scanning 'mytable' table... Table: 'mytable' (1143675122); index ID: 2, database ID: 6 LEAF level scan performed. - Pages Scanned................................: 8337 - Extents Scanned..............................: 1052 - Extent Switches..............................: 1563 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 66.69% [1043:1564] - Logical Scan Fragmentation ..................: 7.81% - Extent Scan Fragmentation ...................: 65.68% - Avg. Bytes Free per Page.....................: 980.4 - Avg. Page Density (full).....................: 87.89% DBCC SHOWCONTIG scanning 'mytable' table... Table: 'mytable' (1143675122); index ID: 3, database ID: 6 LEAF level scan performed. - Pages Scanned................................: 12668 - Extents Scanned..............................: 1595 - Extent Switches..............................: 9829 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 16.11% [1584:9830] - Logical Scan Fragmentation ..................: 37.06% - Extent Scan Fragmentation ...................: 45.89% - Avg. Bytes Free per Page.....................: 2991.4 - Avg. Page Density (full).....................: 63.04% DBCC SHOWCONTIG scanning 'mytable' table... Table: 'mytable' (1143675122); index ID: 4, database ID: 6 LEAF level scan performed. - Pages Scanned................................: 13537 - Extents Scanned..............................: 1709 - Extent Switches..............................: 8791 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 19.26% [1693:8792] - Logical Scan Fragmentation ..................: 29.40% - Extent Scan Fragmentation ...................: 50.03% - Avg. Bytes Free per Page.....................: 3283.1 - Avg. Page Density (full).....................: 59.44% DBCC execution completed. If DBCC printed error messages, contact your system administrator.