Find the Biggest Table in a Database in SQL Server


I do come across a lot of times when the beginners omit on checking the table size before making an alteration at table level. It is obvious that the size of the table is completely linked with the performance of your query. Additionally, in production servers, checking the table structure should be considered a mandated step. For instance, applying changes like the addition of an index can impact the server performance drastically.

Let’s look at the two ways to find the biggest table in a database:

1. By using a query to gather the table size information

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
       --and t.name like '%your table name%' --if desired to take just for few tables
GROUP BY
    t.name, i.object_id, i.index_id, i.name
ORDER BY
    object_name(i.object_id)

2. By using GUI to gather the table size information

   Please follow the below steps in your SSMS

    1. Right-click on the desired “database name” and hover on the “Reports” option.



    2. From the “Report” option, hover on “Standard Reports” and finally select “Disk Usage by Top Tables”.


Once the “Disk Usage by Top Tables” option has been selected, you will find a report something as below. 


This report can be imported to a table and use as a weekly monitoring report with respect to the table size. 

0 comments: