SQL: Check Fragmentation with DBCC SHOWCONTIG
Author: jason
Date: 2014-01-31 15:02:19
Category: Musing

If your database is SQL 2000 it is not possible to use the Dynamic Management View sys.dm_db_index_physical_stats to check the Fragmentation. I wrote a Temp Table and inserted the results of DBCC SHOWCONTIG into it, selecting out only what I wanted to see.

This feature is being removed in future versions of SQL. I list it here in case your databases are SQL 2000 or older.
http://technet.microsoft.com/en-us/library/ms175008.aspx

I used the switches TABLERESULTS and ALL_INDEXES. Note that on a large database this may take some time to run and return its results.




If you don't specify the TABLERESULTS switch the output looks like this.




Code

DECLARE @ShowContig AS TABLE (ObjectName VARCHAR(255), ObjectID INT,
IndexName VARCHAR(255), IndexID INT, [Level] INT, Pages INT,
[Rows] INT, MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize DECIMAL, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes DECIMAL, AveragePageDensity DECIMAL,
ScanDensity DECIMAL, BestCount INT, ActualCount INT,
LogicalFragmentation DECIMAL, ExtentFragmentation DECIMAL);
INSERT INTO @ShowContig
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;')

SELECT ObjectName, IndexName, Pages, Rows,
LogicalFragmentation, ExtentFragmentation
FROM @ShowContig
ORDER BY LogicalFragmentation DESC





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009