SQL Server DBCC Commands: DBCC SHOWCONTIG

DBCC SHOWCONTIG is a has been announced to be deprecated for more than 10 years ago, meaning it may disappear in future versions of SQL Server.

DBCC SHOWCONTIG is a DBCC command in Microsoft SQL Server that can be used to display information about the extent fragmentation of a table or index. Extent fragmentation occurs when the data pages of a table or index are not stored consecutively on disk, which can lead to decreased performance as the database engine must access multiple extents to retrieve the data.

To use DBCC SHOWCONTIG, you must specify the name of the table or index you want to check for fragmentation. You can also specify the name of a specific index or partition within the table, if applicable.

Here is an example of how to use DBCC SHOWCONTIG to check the extent fragmentation of a table:

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

The output of DBCC SHOWCONTIG will include information about the number of extents used by the table or index, the number of pages in each extent, and the percentage of pages that are contained in a single extent. It will also include a fragmentation score, which indicates the degree of fragmentation present in the table or index. A score of 0 indicates no fragmentation, while a higher score indicates more fragmentation.

In addition to the extent fragmentation information, DBCC SHOWCONTIG will also display the number of page splits that have occurred in the table or index, as well as the average number of bytes per page. These metrics can be useful for identifying performance issues or potential issues with index or table design.

To resolve extent fragmentation, you can use the DBCC INDEXDEFRAG or DBCC REINDEX commands to defragment the table or index. These commands can help to improve the performance of queries that access the table or index, as the data pages will be stored consecutively on disk.

I hope this helps give you an understanding of DBCC SHOWCONTIG and how it can be used to check for extent fragmentation in SQL Server. Let me know if you have any further questions.