Wouldso would Time passes about locating the biggest objects inside a SQL Server database? First, by identifying which tables (and related indices) would be the biggest after which identifying which rows inside a particular table are biggest (we are storing binary data in BLOBs)?
What are the tools available for helping with this particular type of database analysis? Or exist some simple queries I possibly could run from the system tables?
I have used this SQL script (that we got from someone, somewhere - can't rebuild who it originated from) for age range and it is assisted me a great deal understanding and identifying how big 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)
Obviously, you should use another ordering criteria, e.g.
ORDER BY SUM(p.rows) DESC
to obtain the tables most abundant in rows, or
ORDER BY SUM(a.total_pages) DESC
to obtain the tables most abundant in pages (8K blocks) used.
If you work with Sql Server Management Studio 2008 you will find certain data fields you will see within the object explorer particulars window. Simply browse to and choose the tables folder. Within the particulars view you'll be able to right-click on the column game titles and add fields towards the "report". Your mileage can vary if you're on SSMS 2008 express.