The DBCC SHOWCONTIG command is a T-SQL command used in Microsoft SQL Server to check the fragmentation of a table or an index. It provides information about the physical storage of the data in the specified table or indexed view, including details about the number of pages, extent counts, and the level of fragmentation.
Here’s how to use the DBCC SHOWCONTIG command:
Syntax
DBCC SHOWCONTIG
(
{ table_name | index_name | table_id | index_id }
[ , NOLOCK ]
)
- table_name: The name of the table for which you want to check fragmentation.
- index_name: The name of the specific index you want to analyze (optional).
- table_id: The object ID of the table (optional).
- index_id: The index ID (optional, default is 0 for heap or primary index).
- NOLOCK: Specifies that the command will run without taking locks (optional).
Example Usage
1. Check Fragmentation of a Table:
DBCC SHOWCONTIG('your_table_name');
Replace your_table_name with the name of the table you want to analyze.
2. Check Fragmentation of a Specific Index:
DBCC SHOWCONTIG('your_table_name', 'your_index_name');
Replace your_table_name with the name of the table and your_index_name with the name of the index.
3. Using Table ID:
If you prefer to use the table ID, you can do it as follows:
DBCC SHOWCONTIG(OBJECT_ID('your_table_name'));
Understanding the Output
When you run DBCC SHOWCONTIG, you will receive output that includes several important metrics:
- Scan Count: Number of times the table or index was scanned.
- Logical Scan Fragmentation: Percentage of fragmentation based on logical page ordering.
- Extent Fragmentation: Percentage of fragmentation based on extents used.
- Page Count: Total number of pages allocated.
- Min/Max/Avg Page Density: Density of the pages in the indexes.
- Avg Fragment Size: Average size of the fragmented sections.
Important Notes
- DBCC SHOWCONTIG is mainly used in older versions of SQL Server. For SQL Server 2005 and later, it is recommended to use sys.dm_db_index_physical_stats for checking fragmentation, as it provides a more detailed and accurate representation of index statistics.
- Always remember that running DBCC commands can have an impact on performance, especially on large tables. It's advisable to run them during maintenance windows or periods of low activity.
Example with sys.dm_db_index_physical_stats
Here's an alternative and recommended way using the sys.dm_db_index_physical_stats function:
SELECT
DB_NAME(database_id) AS DatabaseName,
OBJECT_NAME(object_id) AS TableName,
index_id,
partition_number,
record_count,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('your_table_name'), NULL, NULL, 'DETAILED');
This query will give you a comprehensive overview of the fragmentation status along with record counts and other details.