DBCC Showcontig - Explained

DBCC SHOWCONTIG(t1, i1)
The output of the DBCC SHOWCONTIG statement is as follows:
DBCC SHOWCONTIG scanning 't1' table...
Table: 't1' (541244983); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 25
- Extents Scanned..............................: 5
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 5.0
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 48.00%
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 4862.4
- Avg. Page Density (full).....................: 39.93%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
This output represents the following:
Pages Scanned : This is the number of pages in the index or the table. In the preceding example, four rows can fit on one page, since the average row size, with three INT s and one CHAR(2000), is 2012 (= 4 + 4 + 4 + 2,000) bytes. As there are 40 rows, then ideally the number of pages should be 10 (= 40 / 4). A high value of 25 indicates a large amount of internal fragmentation.

Extents Scanned: This is the number of extents for the index or the table. In the preceding example there are 25 pages. Therefore, the minimum number of extents should be 4 (= 25 / 8, rounded to the higher integer value). A value of 5 indicates that there is some, but not a significant amount of, fragmentation within the extents.

Extent Switches: This is the number of switches required between the extents to access pages in the logical order of the index. In an ideal situation, if all the pages of an index (or table) are laid out in the same order as that of the index,
then Extent Switches = Extents Scanned – 1.
In the preceding example, the ideal number of Extent Switches = (Extents Scanned − 1) = (5 − 1) = 4. A value greater than 4 indicates that the pages in the extents are not in the same order as the logical order of the index. The current high value of 10 is a sign of a large amount of external fragmentation.

Avg. Pages per Extent:
The average number of pages in an extent is = Pages Scanned/Extents Scanned.
For a large table with minimal external fragmentation, Avg. Pages per Extent should be close to 8. For large tables, anything less than 8 indicates external fragmentation.

For small tables, however, Avg. Pages per Extent can be less than 8. For example, a small table with only two pages will have Avg. Pages per Extent as 2, which should not be considered as an external fragmentation since the small number in this case is not due to fragmented content, but a small amount of content.

Scan Density [Best Count:Actual Count]: This is the ratio of the Best Count of extents to the Actual Counts, and it is one of the most useful indicators of fragmentation.
The Best Count represents the number of extents required for the number of Pages Scanned. It is equal to Pages Scanned / 8, rounded to the higher integer number. For example, if Pages Scanned is 9, then Best Count is 9 / 8, which rounded to the higher integer number is 2.
The Actual Count is an indicator of how many extents can ideally cause the current number of Extent Switches. As mentioned in the preceding description of the Extent Switches, Actual Count = Extent Switches + 1.
In the best case, the Actual Count should be equal to the Best Count. Therefore, ideally, Scan Density should be 100%. A value less than 100% indicates that the pages are non-contiguously distributed between the extents. An index with a Scan Density of less than 40% can be considered a candidate for defragmentation.
In the preceding example
Best Count = Pages Scanned / 8, rounded to the higher integer value
= 25 / 8, rounded to the higher integer value
= 4
and Actual Count = Extent Switches + 1 = 10 + 1 = 11
Therefore Scan Density = 4:11 = 36.36%
A low value of 36.36% indicates a high amount of external fragmentation.

Logical Scan Fragmentation: This shows the ratio of pages with a different physical order from the logical order of the index. A range between 0% and 10% is considered an acceptable value.
In the preceding example, the high value of 48.00% indicates that 12 out of 25 pages are out of order, which is a sign of high external fragmentation.

Extent Scan Fragmentation: This indicates the gaps between the extents. Ideally, all the extents used by an index should be side by side on the disk. If a gap exists between two adjacent extents, then the next extent can't be retrieved by the read-ahead operation on the disk. The value of Extent Scan Fragmentation represents the ratio of the total number of gaps between extents to the total number of extents. For instance, if two adjacent extents aren't side by side, then it represents one gap. For a large table using uniform extents, a high Extent Scan Fragmentation value is a sign of high external fragmentation. Ideally, the value of Extent Scan Fragmentation should be 0%, indicating that there are no gaps between the extents.
In the preceding example, a value of 40.00% indicates that out of the five extents, two have gaps from their adjacent extents. This is a sign of high external fragmentation.

Avg. Bytes Free per Page: This is the average number of free bytes in a page. A high value may be due to a large amount of internal fragmentation. This value can also be high if you intentionally maintain lots of free space per page, using the fill factor to reduce page splits caused by INSERT and UPDATE queries. You'll learn about the fill factor later in the chapter.
In the preceding example, a high value of 4,862.4 bytes indicates that, on average, more than half of the pages are empty. This is a sign of high internal fragmentation.

Avg. Page Density (full): This is the inverse of Avg. Bytes Free per Page expressed as percentage. A high percentage value indicates that a greater number of rows have been compressed in the pages.
In the preceding example, the low value of 39.93% represents the same information as represented by the high value of Avg. Bytes Free per Page. It is an indication of a high amount of internal fragmentation.

For a large table, DBCC SHOWCONTIG may take a long time to provide the detailed fragmentation report. To get a quick report on the fragmentation status, you can execute the DBCC SHOWCONTIG statement with the FAST option:DBCC SHOWCONTIG(t1, i1) WITH FAST

This provides a short report:
DBCC SHOWCONTIG scanning 't1' table...
Table: 't1' (541244983); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 25
- Extent Switches..............................: 10
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 48.00%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
You can use this report to decide whether or not a detailed fragmentation analysis is required.

Internal Fragementation:
Number of leaf (or data) pages = Pages Scanned = 10
Average free space in a page = Avg. Bytes Free per Page = 12.0 bytes
Amount of compression in a page = Avg. Page Density (full) = 99.85%

External fragmentation:
A minimum of two extents is required to hold the ten pages. For a minimum of external fragmentation, there should not be any gap between the two extents, and all pages should be physically arranged in the logical order of the index. You can observe these aspects in the preceding output:

1.Number of extents = Extents Scanned = 2.

2. Amount of gap between the extents = Extent Scan Fragmentation = 0.00%.
In case of a nonzero value, determine the number of gaps between the extents. For example, 50% would indicate that one gap exists between the two extents. Compare this number of gaps with the original number of gaps. A lower value is always better.

3. Number of switches between the two extents required to follow the logical order of the pages = Extent Switches = 1.

4. Contiguous distribution of pages across extents = Scan Density [Best Count:Actual Count] = 100.00%.

5. Number of out-of-order pages = Logical Scan Fragmentation = 0.00%. As mentioned previously, a value between 0% and 10% is considered acceptable