Finding the Largest Tables in SQL Server for Performance Optimization

Finding the Largest Tables in SQL Server for Performance Optimization

When optimizing database performance, one of the key areas to focus on is index design. Often, slow queries are caused by inefficient indexing, especially when dealing with large tables. Identifying the largest tables in your database can help you prioritize index tuning and optimize storage.

Identifying the Top 20 Largest Tables in SQL Server

The following SQL query will return to the top 20 largest tables in your SQL Server database based on the amount of space they consume. It calculates both used and allocated space for each table:

SELECT TOP 20

    SCHEMA_NAME(tab.schema_id) + '.' + tab.name AS [Table],

    CAST(SUM(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS Used_MB,

    CAST(SUM(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS Allocated_MB

FROM sys.tables tab

JOIN sys.indexes ind ON tab.object_id = ind.object_id

JOIN sys.partitions part ON ind.object_id = part.object_id AND ind.index_id = part.index_id

JOIN sys.allocation_units spc ON part.partition_id = spc.container_id

GROUP BY SCHEMA_NAME(tab.schema_id) + '.' + tab.name

ORDER BY SUM(spc.used_pages) DESC;