DBCC SHOWCONTIG: A Deprecated Command in SQL Server

For many SQL Server administrators and developers, DBCC SHOWCONTIG was once a go-to tool for diagnosing index and table fragmentation. However, Microsoft announced its deprecation over a decade ago, signaling its eventual removal in a future version of SQL Server. While it remains functional in older versions, relying on it in newer systems is no longer recommended.

What is DBCC SHOWCONTIG?

DBCC SHOWCONTIG is a Database Console Command (DBCC) in SQL Server designed to analyze the fragmentation of tables and indexes. Fragmentation occurs when data pages or extents—logical groups of 8 pages—become scattered rather than stored consecutively on disk. Excessive fragmentation can degrade performance by forcing the SQL Server engine to access non-sequential disk locations, increasing read and write times.

How DBCC SHOWCONTIG Works

The command outputs metrics about table and index fragmentation, such as:

• Extent fragmentation: The percentage of extents (8-page units) that are not contiguous.
• Logical scan fragmentation: Indicates how logically contiguous the pages are during a table or index scan.
• Average page density: Reflects how full the pages are with data.
• Page splits: Tracks page splits resulting from row insertions or updates, which can lead to fragmentation.

A simple example of running DBCC SHOWCONTIG:

-- Check the extent fragmentation of the "Customers" tableDBCC SHOWCONTIG ('Customers');    

The output includes a range of information helpful for diagnosing fragmentation levels. For example:

• Scan Density: Ideal value is close to 100%.
• Logical Fragmentation: A percentage indicating the fragmentation severity.
• Extent Switches: The number of times the scan had to move to a new extent.

Why DBCC SHOWCONTIG Was Deprecated

Microsoft introduced newer, more efficient methods to analyze and address fragmentation, replacing the functionality of DBCC SHOWCONTIG. The main replacement is the sys.dm_db_index_physical_stats dynamic management function (DMF). This function provides more detailed and accurate information about fragmentation and does not require parsing console output like SHOWCONTIG does.

Alternatives to DBCC SHOWCONTIG

The modern approach to fragmentation analysis and resolution involves the following tools and commands:

1. sys.dm_db_index_physical_stats

This DMF returns information similar to DBCC SHOWCONTIG, including fragmentation metrics for indexes and tables.

SELECT     OBJECT_NAME(object_id) AS TableName,    index_id AS IndexID,    avg_fragmentation_in_percent AS FragmentationPercentageFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')WHERE avg_fragmentation_in_percent > 10; -- Filter high fragmentation    

2. Index Maintenance Commands

After identifying fragmentation, you can resolve it using commands such as:

• ALTER INDEX … REBUILD: Rebuilds the index, fully defragmenting and reordering data pages.
• ALTER INDEX … REORGANIZE: Reorganizes the leaf-level pages of the index, addressing minor fragmentation without locking resources heavily.

ALTER INDEX ALL ON Customers REBUILD; -- Rebuild all indexes on a table    

3. Database Maintenance Plans

For routine index maintenance, many SQL Server environments leverage automated maintenance plans to regularly rebuild or reorganize indexes based on fragmentation thresholds.

Why You Should Avoid DBCC SHOWCONTIG

Despite its historical usefulness, DBCC SHOWCONTIG is outdated and limited in functionality compared to modern tools. Microsoft has not supported it in newer SQL Server enhancements, meaning:

• It does not handle partitioned tables and indexes effectively.
• Its output is cumbersome to interpret compared to the detailed, structured results of sys.dm_db_index_physical_stats.
• Future SQL Server versions may remove the command entirely, breaking scripts that rely on it.

Moving Forward with Best Practices

To maintain optimal SQL Server Performance, here are some tips:

1. Adopt Modern Tools: Replace DBCC SHOWCONTIG with sys.dm_db_index_physical_stats in your scripts and processes.
2. Automate Index Maintenance: Use tools or scripts to automate index reorganization and rebuilding. For example, Ola Hallengren’s popular SQL Server Maintenance Solution is widely used for this purpose.
3. Monitor Fragmentation with Database Health Monitor: Database Health Monitor is a free tool developed by Stedman Solutions, LLC, offering a user-friendly interface for monitoring SQL Server health, including index fragmentation metrics.
4. Leverage Managed Services: Regular index maintenance is crucial for SQL Server health. Consider outsourcing this to experts. Stedman Solutions’ SQL Server Managed Services include proactive monitoring, regular index maintenance, and automated alerting, ensuring your SQL Server performs at its best.

For help with SQL Server maintenance, Performance Tuning, or monitoring, contact us.