MSSQL Table Analysis Queries

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

 


 

Related Scribbles:
  • MSSQL - List size (on disk) of all tables in a database
  • Rename MSSQL Server Database
  • SQL Server - List Fill Factor of all Indexes
  • SQL Server


  • ID: 727
    Author:
    leonard
    Date Updated:
    2014-06-16 11:33:01
    Date Created:
    2005-12-29 12:30:41

    Edit

    Comments?
     >> Leonard Chan's Homepage  >> Scribble Web  >> MSSQL Table Analysis Queries
    leonard.lotus-land.ca is hosted by Perceptus Solutions Inc.