DBCC SHOWCONTIG in Microsoft SQL Server

DBCC SHOWCONTIG

Displays fragmentation information for the data and indexes of the specified table.

Syntax

DBCC SHOWCONTIG
[    ( { table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
)
]
[ WITH { ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ]
[ , { FAST | ALL_LEVELS } ]
}
]

Remarks
The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If onlytable_id is specified, or if index_id is 0, the data pages of the specified table are scanned.

DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) made against the table. Because these modifications are not usually distributed equally among the rows of the table, the fullness of each page can vary over time. For queries that scan part or all of a table, such table fragmentation can cause additional page reads, which hinders parallel scanning of data.

When an index is heavily fragmented, there are two choices for reducing:
• Drop and re-create a clustered index.
Re-creating a clustered index reorganizes the data, and results in full data pages. The level of fullness can be configured using the FILLFACTOR option. The drawbacks of this method are that the index is offline during the drop/re-create cycle and that the operation is atomic. If the index creation is interrupted, the index is not re-created.
• Use DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order.
The DBCC INDEXDEFRAG command is an online operation, so the index is available while the command is running. The operation is also interruptible without loss of completed work. The drawback of this method is that it does not do as good a job of reorganizing the data as a clustered index drop/re-create operation.
The Avg. Bytes free per page and Avg. Page density (full) statistic in the result set give an indication of the fullness of index pages. The Avg. Bytes free per page figure should be low and the Avg. Page density (full) figure should be high. Dropping and recreating a clustered index, with the FILLFACTOR option specified, can improve these statistics. Also, the DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improve these statistics.

The fragmentation level of an index can be determined in two ways:
• Comparing the values of Extent Switches and Extents Scanned.
Note: This method of determining fragmentation does not work if the index spans multiple files. The value of Extent Switchesshould be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value, which should be as high as possible. This can be improved by either method of reducing fragmentation discussed earlier.
• Understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.
Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table’s fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both methods of reducing fragmentation can be used to reduce these values.

Result Sets
This table describes the information in the result set.
Statistic Description
Pages Scanned Number of pages in the table or index.
Extents Scanned Number of extents in the table or index.
Extent Switches Number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index.
Avg. Pages per Extent Number of pages per extent in the page chain.
Scan Density
[Best Count: Actual Count] Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
Logical Scan Fragmentation Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Avg. Bytes free per page Average number of free bytes on the pages scanned. The higher the number, the less full the pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number.
Avg. Page density (full) Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

When a table ID and the FAST option are specified, DBCC SHOWCONTIG returns a result set with only the following columns:
 Pages Scanned
 Extent Switches
 Scan Density [Best Count:Actual Count]
 Logical Scan Fragmentation
 When TABLERESULTS is specified, DBCC SHOWCONTIG returns these eight columns, described in the first table, and the following additional columns.
 ExtentSwitches
 AverageFreeBytes
 AveragePageDensity
 ScanDensity
 BestCount
 ActualCount
 LogicalFragmentation
 ExtentFragmentation

Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the index. The level number increases moving up the tree toward the index root. Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire heap.
Rows Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or entire heap.
MaximumRecordSize Maximum record size in that level of the index or entire heap.
AverageRecordSize Average record size in that level of the index or entire heap.
ForwardedRecords Number of forwarded records in that level of the index or entire heap.
Extents Number of extents in that level of the index or entire heap.

DBCC SHOWCONTIG returns the following columns when TABLERESULTS and FAST are specified.
• ObjectName
• ObjectId
• IndexName
• IndexId
• Pages
• ExtentSwitchs
• ScanDensity
• BestCount
• ActualCount
• LogicalFragmentation

Permissions
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner, and are not transferable.

Examples
A. Display fragmentation information for a table
This example displays fragmentation information for the table with the specified table name.

USE Northwind
GO
DBCC SHOWCONTIG (Employees)
GO

B. Use OBJECT_ID to obtain the table ID and sysindexes to obtain the index ID
This example uses OBJECT_ID and sysindexes to obtain the table ID and index ID for the aunmind index of the authors table.

USE pubs
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('authors')
SELECT @indid = indid
FROM sysindexes
WHERE id = @id
AND name = 'aunmind'
DBCC SHOWCONTIG (@id, @indid)
GO

C. Display an abbreviated result set for a table
This example returns an abbreviated result set for the authors table in the pubs database.

USE pubs
DBCC SHOWCONTIG ('authors', 1) WITH FAST

D. Display the full result set for every index on every table in a database
This example returns a full table result set for every index on every table in the pubs database.

USE pubs
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s