SQL: Check Fragmentation with DBCC SHOWCONTIG
Date: 2014-01-31 15:02:19
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.
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.
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,
ORDER BY LogicalFragmentation DESC