A great set of tsql queries for Microsoft SQL Server and MSDE to figure out which tables in the database are the biggest, most fragmented, have the most rows, most in need of a defragmentation or optimization etc.
Update: rats, I knew I should have copied the entire article, it appears to be offline or moved.
http://www.sqlservercentral.com/columnists/jsack/analyzingdatastoragetenshowcontigqueries_printversion.asp
Searching for analyzing data storage sqlserver eventually found the document again, free registraton required.
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/analyzingdatastoragetenshowcontigqueries/1438/
Basically, this query creates a temp table:
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
)
This populates it:
INSERT #ShowContig EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS')
Then query the temp table, e.g.
SELECT TOP 10 ObjectName, IndexName, logicalfrag, CountPages
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
ObjectName NOT LIKE 'sys%'
ORDER BY logicalfrag DESC, CountPages DESC
Note: there is a bit of text that suggests using the ALL_INDEXES keyword for SHOWCONTIG. The syntax for that is "EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')". I.e. add a comma and the ALL_INDEXES option at the end.
Also demonstrates the use of creating a temporary table with the hash mark.
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')
Tags: sqlserver, mssql, tables, query analyzer, optimize, logical, fragments, fragmentation, msde