How to find the largest objects in a SQL Server database?

Question

How do I find the largest objects in a SQL Server database? First, by determining which tables (and associated indexes) are the largest, and then determining which rows in a particular table are the largest (we store binary data in a BLOB)?

Are there any tools to help with this kind of database analysis? Or are there some simple queries that I could execute against system tables?

Answer 1

I used this SQL script (which I received from someone, somewhere - I can’t recover from whom it came from) for ages, and it helped me to understand and determine the size of indexes and tables a bit:

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 other order criteria, for example

 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 used (8K blocks).

Answer 2

In SQL Server 2008, you can also simply run the standard disk usage "Disk Usage by Top Tables" drive. This can be found by right-clicking the database, selecting reports-> standard reports and selecting the desired report.

Answer 3

You can also use the following code:

 USE AdventureWork GO CREATE TABLE #GetLargest ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50) ) SET NOCOUNT ON INSERT #GetLargest EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #GetLargest a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #GetLargest 

Answer 4

This query will help you find the largest table in you - this is the join.

 SELECT TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 ORDER BY st.row_count DESC 

Answer 5

If you are using Sql Server Management Studio 2008, there are certain data fields that you can view in the object explorer details window. Just browse and select the folder with the tables. In the detail view, you can right-click the column headings and add fields to the report. Your mileage may vary if you use SSMS 2008 Express.

Answer 6

I found this query very useful in SqlServerCentral, here is the link to the original post

Maximum Sql Server Tables

  select name=object_schema_name(object_id) + '.' + object_name(object_id) , rows=sum(case when index_id < 2 then row_count else 0 end) , reserved_kb=8*sum(reserved_page_count) , data_kb=8*sum( case when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count else lob_used_page_count + row_overflow_used_page_count end ) , index_kb=8*(sum(used_page_count) - sum( case when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count else lob_used_page_count + row_overflow_used_page_count end ) ) , unused_kb=8*sum(reserved_page_count-used_page_count) from sys.dm_db_partition_stats where object_id > 1024 group by object_id order by rows desc 

In my database, they gave different results between this query and the first answer.

Hope someone finds useful

Answer 7

@Marc_s answer is very good and I have been using it for several years now. However, I noticed that the script skips data in some columnstore indices and does not show the whole picture. For example, when you execute SUM(TotalSpace) for a script and compare it with the property of the shared space database in Management Studio, in my case the numbers do not match (Management Studio shows large numbers). I modified the script to overcome this problem and expanded it a bit:

 select tables.[name] as table_name, schemas.[name] as schema_name, isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown') as database_name, sum(allocation_units.total_pages) * 8 as total_space_kb, cast(round(((sum(allocation_units.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as total_space_mb, sum(allocation_units.used_pages) * 8 as used_space_kb, cast(round(((sum(allocation_units.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as used_space_mb, (sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8 as unused_space_kb, cast(round(((sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unused_space_mb, count(distinct indexes.index_id) as indexes_count, max(dm_db_partition_stats.row_count) as row_count, iif(max(isnull(user_seeks, 0)) = 0 and max(isnull(user_scans, 0)) = 0 and max(isnull(user_lookups, 0)) = 0, 1, 0) as no_reads, iif(max(isnull(user_updates, 0)) = 0, 1, 0) as no_writes, max(isnull(user_seeks, 0)) as user_seeks, max(isnull(user_scans, 0)) as user_scans, max(isnull(user_lookups, 0)) as user_lookups, max(isnull(user_updates, 0)) as user_updates, max(last_user_seek) as last_user_seek, max(last_user_scan) as last_user_scan, max(last_user_lookup) as last_user_lookup, max(last_user_update) as last_user_update, max(tables.create_date) as create_date, max(tables.modify_date) as modify_date from sys.tables left join sys.schemas on schemas.schema_id = tables.schema_id left join sys.indexes on tables.object_id = indexes.object_id left join sys.partitions on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id left join sys.allocation_units on partitions.partition_id = allocation_units.container_id left join sys.dm_db_index_usage_stats on tables.object_id = dm_db_index_usage_stats.object_id and indexes.index_id = dm_db_index_usage_stats.index_id left join sys.dm_db_partition_stats on tables.object_id = dm_db_partition_stats.object_id and indexes.index_id = dm_db_partition_stats.index_id group by schemas.[name], tables.[name], isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown') order by 5 desc 

Hope this will be helpful to someone. This script has been tested with large databases throughout the TB with hundreds of different tables, indexes, and schemas.