Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Analyzing Data Storage - Ten SHOWCONTIG Queries

By Joseph Sack,

Analyze Data Storage - Ten SHOWCONTIG Queries

Analyze Data Storage - Ten SHOWCONTIG Queries

By Joseph Sack

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.

Total article views: 22453 | Views in the last 30 days: 22
 
Related Articles
FORUM

DBCC SHOWCONTIG and indid

DBCC SHOWCONTIG and indid

FORUM

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES question

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES question

FORUM

clustered index

clustered index

SCRIPT

Query to get tables with no clustered indexes

Query to get tables with no clustered indexes(2K5,2K8,2K8 R2)

FORUM

Clustered Index

SQL Server(clustered Index)

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones