DBCC SHOWCONTIG allows you to report on fragmentation information for the data and indexes of a specified table.

If neither an index nor table is specified, DBCC SHOWCONTIG will report on all tables within the database. You can capture this output by adding "WITH TABLERESULTS" to the DBCC SHOWCONTIG call. SHOWCONTIG allows you to grab all sorts of useful information, so that you can report or act upon it with your Transact-SQL code.

In this article, we will be reviewing how you can use Transact-SQL queries to probe your database's potential trouble areas...

Prerequisites: The basics of SHOWCONTIG are not reviewed here, and if you're in need of this background, check out SQL Server Books Online's topic "DBCC SHOWCONTIG".

Query #1

List all the tables that don't have clustered indexes (excluding system tables).

Prior to running this first query, we first need to create the #SHOWCONTIG temporary table. This table will be used to hold the results of the DBCC SHOWCONTIG result set.

CREATE TABLE #SHOWCONTIG (
   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)

Next, populate the #SHOWCONTIG table with all heaps and clustered indexes within the database you wish to analyze. (If you wish to report on all indexes within the database, add the ALL_INDEXES keyword to your DBCC SHOWCONTIG execution).

INSERT #ShowContig
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS')

Now we are ready to list all the tables that don't have clustered indexes (excluding system tables). Within the #ShowContig table, if a specific row has an index name, this means the row is for a clustered index. If the index name is blank, the row represents a heap (no clustered index).

SELECT ObjectName, 
ObjectID 
FROM #ShowContig
WHERE LEN(IndexName)=0 AND
ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY ObjectName

For tables that appear in this result set, consider adding a clustered index. Some DBAs argue that it isn't necessary for all tables; however I think that clustered indexes should be added by default - and only removed if you have a very good reason to do so.

Query #2

Show the top ten tables with the most data pages (does not include non-clustered index pages).

SELECT  TOP 10
ObjectName, 
IndexName,
countpages
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  countpages DESC

This query is mostly for informational purposes. You may be surprised by the results.

Query #3

Show the top ten tables with the highest row counts.

SELECT  TOP 10
ObjectName, 
IndexName,
CountRows
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  CountRows DESC

Once again, this query is mostly for informational purposes. If the row counts surprise you - investigate.

Query #4

List the top ten tables with the largest average record size.

SELECT  TOP 10
ObjectName, 
IndexName,
AvgRecSize
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  AvgRecSize DESC

The smaller the average record size, the more rows you can fit on a single page. The more you can fit on a page (assuming a high index fill factor), the fewer pages you have to read in order to fulfill the query requirements. In other words, smaller average record sizes mean less I/O.

If the average is high, see if all the table columns are being used, and that the column data types are appropriate.

Query #5

List the top ten tables with the largest record sizes.

SELECT  TOP 10
ObjectName, 
IndexName,
MaxRecSize
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  MaxRecSize DESC

Like the average record size, this query can help you determine which tables have at least one large row. Keep in mind that all it takes is ONE large row within a table to get on the top 10 of this list - so consider this in the context of the average row width. If you see tables falling into both lists (average row width and largest record size), see if all the table columns are being used, and that the column data types are appropriate.

Query #6

Show the top ten tables with the highest average bytes free per page.

SELECT  TOP 10
ObjectName, 
IndexName,
AvgFreeBytes
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  AvgFreeBytes DESC

Generally, the average bytes free value should be low, so investigate high values (for larger tables). "Low" and "high" are relative terms to your database , but be sure to check your index fill-factor. Is your index fill-factor too low? If you're not performing that many insert/update operations, consider increasing the fill-factor and rebuilding your indexes - thus decreasing I/O.

Query #7

Show the top ten tables with the LOWEST average page density.

SELECT  TOP 10
ObjectName, 
IndexName,
AvgPageDensity
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  AvgPageDensity ASC

Generally, average page density should be high. Investigate low densities (for larger tables) closely. If you determine that fragmentation is an issue, recreating/rebuilding the clustered index will reorganize the data, resulting in full data pages (depending on the index fill-factor). If rebuilding the indexes or using DBCC DBREINDEX is not possible (the index is offline during the drop/re-create cycle ), consider the less effective DBCC INDEXDEFRAG.

Query #8

List the top ten tables with the highest amount of logical fragmentation.

SELECT  TOP 10
ObjectName, 
IndexName,
logicalfrag
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY  logicalfrag DESC

Investigate larger tables that appear on this list. Like the previous query, if you determine that fragmentation is an issue, adjust the fill-factor if necessary, recreate/rebuild the clustered index or execute the less effective DBCC INDEXDEFRAG.

Query #9

List the top ten tables with the highest extent fragmentation.

SELECT  TOP 10
ObjectName, 
IndexName,
ExtentFrag
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY   ExtentFrag DESC

Extent fragmentation should be as low as possible. Investigate larger tables that appear on this list. Like the previous query, if you determine that fragmentation is an issue, recreate/rebuild the clustered index or execute the less effective DBCC INDEXDEFRAG.

Query #10

List the top ten tables with the lowest scan density.

SELECT  TOP 10
ObjectName, 
IndexName,
 ScanDensity 
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
 ObjectName NOT LIKE 'sys%'
ORDER BY    ScanDensity ASC

Scan density should be as high as possible. If low, consider recreating/rebuilding the clustered index or executing the less effective DBCC INDEXDEFRAG.

Conclusion

After examining the output from #ShowContig, don't forget to issue a "DROP TABLE #ShowContig" as a final step.

DROP TABLE #SHOWCONTIG

Use these ten queries to get to know your database better. You may be surprised by what you find. For example: gigantic tables that you were not aware of, tables without clustered indexes, tables with wide row lengths, and tables with significant fragmentation.

Run these queries periodically to help manage both the size of your database and the performance of your queries.