How to find largest objects in a SQL Server database?

Question

How would I go about finding the largest objects in a SQL Server database? First, by determining which tables (and related indices) are the largest and then determining which rows in a particular table are largest (we're storing binary data in BLOBs)?

Are there any tools out there for helping with this kind of database analysis? Or are there some simple queries I could run against the system tables?

Answer 1

I've been using this SQL script (which I got from someone, somewhere - can't reconstruct who it came from) for ages and it's helped me quite a bit understanding and determining the size of indices and tables:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id) 

Of course, you can use another ordering criteria, e.g.

ORDER BY SUM(p.rows) DESC

to get the tables with the most rows, or

ORDER BY SUM(a.total_pages) DESC

to get the tables with the most pages (8K blocks) used.

Answer 2

If you are using Sql Server Management Studio 2008 there are certain data fields you can view in the object explorer details window. Simply browse to and select the tables folder. In the details view you are able to right-click the column titles and add fields to the "report". Your mileage may vary if you are on SSMS 2008 express.