DBCC SHOWCONTIG (Transact-SQL)

DBCC SHOWCONTIG (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

System_CAPS_ICON_important.jpg Important


This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sys.dm_db_index_physical_stats instead.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

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

table_name | table_id | view_name | view_id
Is the table or view to check for fragmentation information. If not specified, all tables and indexed views in the current database are checked. To obtain the table or view ID, use the OBJECT_ID function.

index_name | index_id
Is the index to check for fragmentation information. If not specified, the statement processes the base index for the specified table or view. To obtain the index ID, use the sys.indexes catalog view.

WITH
Specifies options for the type of information returned by the DBCC statement.

FAST
Specifies whether to perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index.

ALL_INDEXES
Displays results for all the indexes for the specified tables and views, even if a particular index is specified.

TABLERESULTS
Displays results as a rowset, with additional information.

ALL_LEVELS
Maintained for backward compatibility only. Even if ALL_LEVELS is specified, only the index leaf level or table data level is processed.

NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.

The following table describes the information in the result set.

StatisticDescription
Pages ScannedNumber of pages in the table or index.
Extents ScannedNumber of extents in the table or index.
Extent SwitchesNumber of times the DBCC statement moved from one extent to another while the statement traversed the pages of the table or index.
Avg. Pages per ExtentNumber of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]Is a percentage. It is the ratio Best Count to Actual Count. This value is 100 if everything is contiguous; if this value is less than 100, some fragmentation exists.

 Best Count is the ideal number of extent changes if everything is contiguously linked. Actual Count is the actual number of extent changes.
Logical Scan FragmentationPercentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
Extent Scan FragmentationPercentage 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 that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

Note: This number is meaningless when the index spans multiple files.
Avg. Bytes Free per PageAverage number of free bytes on the pages scanned. The larger the number, the less full the pages are. Lower numbers are better if the index will not have many random inserts. This number is also affected by row size; a large row size can cause a larger number.
Avg. Page density (full)Average page density, as a percentage. This value takes into account row size. Therefore, the value is a more accurate indication of how full your pages are. The larger the percentage, the better.

When table_id and FAST are specified, DBCC SHOWCONTIG returns a result set with only the following columns.

  • Pages Scanned

  • Extent Switches

  • Scan Density [Best Count:Actual Count]

  • Extent Scan Fragmentation

  • Logical Scan Fragmentation

When TABLERESULTS is specified, DBCC SHOWCONTIG returns the following columns and also the nine columns described in the previous table.

StatisticDescription
Object NameName of the table or view processed.
ObjectIdID of the object name.
IndexNameName of the index processed. Is NULL for a heap.
IndexIdID of the index. Is 0 for a heap.
LevelLevel of the index. Level 0 is the leaf, or data, level of the index.

Level is 0 for a heap.
PagesNumber of pages that make up that level of the index or whole heap.
RowsNumber of data or index records at that level of the index. For a heap, this value is the number of data records in the whole heap.

For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage.
MinimumRecordSizeMinimum record size in that level of the index or whole heap.
MaximumRecordSizeMaximum record size in that level of the index or whole heap.
AverageRecordSizeAverage record size in that level of the index or whole heap.
ForwardedRecordsNumber of forwarded records in that level of the index or whole heap.
ExtentsNumber of extents in that level of the index or whole heap.
ExtentSwitchesNumber of times the DBCC statement moved from one extent to another while the statement traversed the pages of the table or index.
AverageFreeBytesAverage number of free bytes on the pages scanned. The larger the number, the less full the pages are. Lower numbers are better if the index will not have many random inserts. This number is also affected by row size; a large row size can cause a larger number.
AveragePageDensityAverage page density, as a percentage. This value takes into account row size. Therefore, the value is a more accurate indication of how full your pages are. The larger the percentage, the better.
ScanDensityIs a percentage. It is the ratio BestCount to ActualCount. This value is 100 if everything is contiguous; if this value is less than 100, some fragmentation exists.
BestCountIs the ideal number of extent changes if everything is contiguously linked.
ActualCountIs the actual number of extent changes.
LogicalFragmentationPercentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
ExtentFragmentationPercentage 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 that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

Note: This number is meaningless when the index spans multiple files.

When WITH TABLERESULTS and FAST are specified, the result set is the same as when WITH TABLERESULTS is specified, except the following columns will have null values:

RowsExtents
MinimumRecordSizeAverageFreeBytes
MaximumRecordSizeAveragePageDensity
AverageRecordSizeExtentFragmentation
ForwardedRecords

The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified or if index_id is 0, the data pages of the specified table are scanned. The operation only requires an intent-shared (IS) table lock. This way all updates and inserts can be performed, except those that require an exclusive (X) table lock. This allows for a tradeoff between speed of execution and no reduction in concurrency against the number of statistics returned. However, if the command is being used only to gauge fragmentation, we recommend that you use the WITH FAST option for optimal performance. A fast scan does not read the leaf or data level pages of the index. The WITH FAST option does not apply to a heap.

DBCC SHOWCONTIG does not display data with ntext, text, and image data types. This is because text indexes that store text and image data no longer exist.

Also, DBCC SHOWCONTIG does not support some new features. For example:

  • If the specified table or index is partitioned, DBCC SHOWCONTIG only displays the first partition of the specified table or index.

  • DBCC SHOWCONTIG does not display row-overflow storage information and other new off-row data types, such as nvarchar(max), varchar(max), varbinary(max), and xml.

  • Spatial indexes are not supported by DBCC SHOWCONTIG.

All new features are fully supported by the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management view.

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 ordinarily 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. This hinders parallel scanning of data.

When an index is heavily fragmented, the following choices are available for reducing fragmentation:

  • Drop and re-create a clustered index.

    Re-creating a clustered index reorganizes the data, and causes full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks of this method are that the index is offline during the drop or re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created.

  • Reorder the leaf-level pages of the index in a logical order.

    Use ALTER INDEX…REORGANIZE to reorder the leaf-level pages of the index in a logical order. Because this operation is an online operation, the index is available when the statement is running. The operation is also interruptible without loss of completed work. The drawback of this method is that the method does not do as good a job of reorganizing the data as a clustered index drop or re-create operation.

  • Rebuild the index.

    Use ALTER INDEX with REBUILD to rebuild the index. For more information, see ALTER INDEX (Transact-SQL).

The Avg. Bytes free per page and Avg. Page density (full) statistic in the result set indicate the fullness of index pages. The Avg. Bytes free per page number should be low and the Avg. Page density (full) number should be high for an index that will not have many random inserts. Dropping and re-creating an index with the FILLFACTOR option specified can improve the statistics. Also, ALTER INDEX with REORGANIZE will compact an index, taking into account its FILLFACTOR, and will improve the statistics.

System_CAPS_ICON_note.jpg Note


An index that has many random inserts and very full pages will have an increased number of page splits. This causes more fragmentation.

The fragmentation level of an index can be determined in the following ways:

  • By comparing the values of Extent Switches and Extents Scanned.

    The value of Extent Switches should be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value. This value should be as high as possible, and can be improved by reducing index fragmentation.

    System_CAPS_ICON_note.jpg Note


    This method does not work if the index spans multiple files.

  • By understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.

    Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values are the best indicators of the fragmentation level of a table. Both these values should be as close to zero as possible, although a value from 0 through 10 percent may be acceptable.

    System_CAPS_ICON_note.jpg Note


    The Extent Scan Fragmentation value will be high if the index spans multiple files. To reduce these values, you must reduce the index fragmentation.

User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

A. Displaying fragmentation information for a table

The following example displays fragmentation information for the Employee table.

USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG ('HumanResources.Employee');  
GO  

B. Using OBJECT_ID to obtain the table ID and sys.indexes to obtain the index ID

The following example uses OBJECT_ID and the sys.indexes catalog view to obtain the table ID and index ID for the AK_Product_Name index of the Production.Product table in the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DECLARE @id int, @indid int  
SET @id = OBJECT_ID('Production.Product')  
SELECT @indid = index_id   
FROM sys.indexes  
WHERE object_id = @id   
   AND name = 'AK_Product_Name'  
DBCC SHOWCONTIG (@id, @indid);  
GO  

C. Displaying an abbreviated result set for a table

The following example returns an abbreviated result set for the Product table in the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;  
GO  

D. Displaying the full result set for every index on every table in a database

The following example returns a full table result set for every index on every table in the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;  
GO  

E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database

The following example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/  
-- Declare variables  
SET NOCOUNT ON;  
DECLARE @tablename varchar(255);  
DECLARE @execstr   varchar(400);  
DECLARE @objectid  int;  
DECLARE @indexid   int;  
DECLARE @frag      decimal;  
DECLARE @maxfrag   decimal;  
  
-- Decide on the maximum fragmentation to allow for.  
SELECT @maxfrag = 30.0;  
  
-- Declare a cursor.  
DECLARE tables CURSOR FOR  
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME  
   FROM INFORMATION_SCHEMA.TABLES  
   WHERE TABLE_TYPE = 'BASE TABLE';  
  
-- Create the table.  
CREATE TABLE #fraglist (  
   ObjectName char(255),  
   ObjectId int,  
   IndexName char(255),  
   IndexId int,  
   Lvl int,  
   CountPages int,  
   CountRows int,  
   MinRecSize int,  
   MaxRecSize int,  
   AvgRecSize int,  
   ForRecCount int,  
   Extents int,  
   ExtentSwitches int,  
   AvgFreeBytes int,  
   AvgPageDensity int,  
   ScanDensity decimal,  
   BestCount int,  
   ActualCount int,  
   LogicalFrag decimal,  
   ExtentFrag decimal);  
  
-- Open the cursor.  
OPEN tables;  
  
-- Loop through all the tables in the database.  
FETCH NEXT  
   FROM tables  
   INTO @tablename;  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
-- Do the showcontig of all indexes of the table  
   INSERT INTO #fraglist   
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')   
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');  
   FETCH NEXT  
      FROM tables  
      INTO @tablename;  
END;  
  
-- Close and deallocate the cursor.  
CLOSE tables;  
DEALLOCATE tables;  
  
-- Declare the cursor for the list of indexes to be defragged.  
DECLARE indexes CURSOR FOR  
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag  
   FROM #fraglist  
   WHERE LogicalFrag >= @maxfrag  
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;  
  
-- Open the cursor.  
OPEN indexes;  
  
-- Loop through the indexes.  
FETCH NEXT  
   FROM indexes  
   INTO @tablename, @objectid, @indexid, @frag;  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',  
      ' + RTRIM(@indexid) + ') - fragmentation currently '  
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';  
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',  
       ' + RTRIM(@indexid) + ')';  
   EXEC (@execstr);  
  
   FETCH NEXT  
      FROM indexes  
      INTO @tablename, @objectid, @indexid, @frag;  
END;  
  
-- Close and deallocate the cursor.  
CLOSE indexes;  
DEALLOCATE indexes;  
  
-- Delete the temporary table.  
DROP TABLE #fraglist;  
GO  

ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
DBCC (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.dm_db_index_physical_stats (Transact-SQL)
OBJECT_ID (Transact-SQL)
sys.indexes (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft