How to read DBCC SHOWCONTIG ???

[Zubair]

Hello,

I'm new to SQL Server, can somebody please give me a good explaination about whats wrong in the following table stats. I tried to see it in BOL, but I wuold appreciate if someone can suggest me the rules here, for expample what should be the ideal scan density and so on. Also let me know what one should do if there are some problems like mentioned below:

Thanks in advance.
Zubair

DBCC SHOWCONTIG scanning 'OrderDetails' table...
Table: 'OrderDetails' (2141354793); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 24168
- Extents Scanned..............................: 3034
- Extent Switches..............................: 4984
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 60.60% [3021:4985]
- Logical Scan Fragmentation ..................: 65.76%
- Extent Scan Fragmentation ...................: 2.80%
- Avg. Bytes Free per Page.....................: 775.8
- Avg. Page Density (full).....................: 90.42%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[loonbug]

There are a few important pieces of information here:
1. Pages Scaned = the number of physical memory pages that needed to be accessed to do a full table scan. This is good for establishing a baseline for query optimization.

2. Extents Scanned: This shows you the number of extents (8 page blocks) had to be accessed for a table scan. The ideal amount would be 3021 which is 24168 pages/(8 pages per extent). The difference from ideal is the number of pages that are in mixed extents = in your case 3034 - 3021

3. Extent switches is the actual number extents sql had to go through to find the data. The difference from 'Extents scaned' (4984 - 3034 in your case) is the number of pointer left in the leaf level of your clustered index. Pointers are left when you move data around (inserts, updates, deletions) in a table that has clustered index. Sql server has to physically create new pages and leaves forwarding address in the old spot instead of reorganizing in order to save time.

4. The most useful piece of information is scan density. This is the ratio of optimal # of extents accesed (3021 = pages scanned/avg #pages/extent) to # of extents actually accessed 4985 (I don't know why this is one more than 4984). 60% is pretty low. In a small OLTP table this is probably unavoidable and will probably not represent a very noticable performance decrease. In a large DSS table performance would certainly be degraded.

5. The last piece of important information is the avg page density. This tells how much free space is left on the pages for sql server to make modifications. If there is enough space sql server will make inserts within existing exents (in physical order) which avoides the forwarding addressed problem mentioned earlier.


What to do:
1. Scan Density: If you belive scan density is affecting performance then there are a couple routes you can take. a. drop and recreate the index b. do a showcontig indexdefrag (I think this is only available in sql2k)

2. Page Density: if you want to increase the space available for sql to make changes increase the decrease the fillfactor (0% default = 100%). Your on your own figuring out how much space to fill. Note that a decreased fillfactor = larger space allocation.





------------
Zubair at 8/21/01 3:31:46 AM

Hello,

I'm new to SQL Server, can somebody please give me a good explaination about whats wrong in the following table stats. I tried to see it in BOL, but I wuold appreciate if someone can suggest me the rules here, for expample what should be the ideal scan density and so on. Also let me know what one should do if there are some problems like mentioned below:

Thanks in advance.
Zubair

DBCC SHOWCONTIG scanning 'OrderDetails' table...
Table: 'OrderDetails' (2141354793); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 24168
- Extents Scanned..............................: 3034
- Extent Switches..............................: 4984
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 60.60% [3021:4985]
- Logical Scan Fragmentation ..................: 65.76%
- Extent Scan Fragmentation ...................: 2.80%
- Avg. Bytes Free per Page.....................: 775.8
- Avg. Page Density (full).....................: 90.42%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.