Thursday, January 19, 2006


Find out fragmentation levels for all tables in your database by running the undocumented stored procedure sp_MSforeachtable

-- Create temp table
CREATE TABLE #fraglist (
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)

--Insert fragmentation level information
sp_MSforeachtable 'insert into #fraglist exec(''DBCC showcontig (''''?'''') with tableresults '') '

--Get all the info back
SELECT * FROM #fraglist

After you have run this you can determine which tables should be defragmented by looking at the AvgFreeBytes, ScanDensity, LogicalFrag and ExtenFrag columns

No comments: