Which Tables Are Huge: Rows Versus Size in SQL Server

Which tables are huge depends on rows or megabytes, because a table can hold 300,000 rows and stay small. Another table can hold 300 rows and fill more space. A list that shows only one of the two numbers sends you to the wrong table. One query can show both numbers and rank them.

Which Tables Are Huge: Two Tables That Disagree

The demo creates a database named TableSpaceDemo with three tables. Cherries holds 300,000 narrow rows of five bytes of data each. Pumpkins holds 300 rows with a long text column of 42,000 characters. Visits is partitioned by year, so it spreads 3,000 rows over three partitions and carries one nonclustered index. Run the script on a test server.

IF DB_ID(N'TableSpaceDemo') IS NULL CREATE DATABASE TableSpaceDemo;
GO
USE TableSpaceDemo;
GO
DROP TABLE IF EXISTS dbo.Cherries, dbo.Pumpkins, dbo.Visits;
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'psVisitYear') DROP PARTITION SCHEME psVisitYear;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'pfVisitYear') DROP PARTITION FUNCTION pfVisitYear;
CREATE TABLE dbo.Cherries (CherryID int NOT NULL CONSTRAINT PK_Cherries PRIMARY KEY, Weight tinyint NOT NULL);
CREATE TABLE dbo.Pumpkins (PumpkinID int NOT NULL CONSTRAINT PK_Pumpkins PRIMARY KEY, Label varchar(20) NOT NULL, Notes varchar(max) NOT NULL);
CREATE PARTITION FUNCTION pfVisitYear (date) AS RANGE RIGHT FOR VALUES ('2025-01-01', '2026-01-01');
CREATE PARTITION SCHEME psVisitYear AS PARTITION pfVisitYear ALL TO ([PRIMARY]);
CREATE TABLE dbo.Visits (
    VisitID   int NOT NULL,
    VisitDate date NOT NULL,
    Note      char(200) NOT NULL DEFAULT 'x',
    CONSTRAINT PK_Visits PRIMARY KEY (VisitID, VisitDate)
) ON psVisitYear (VisitDate);
CREATE INDEX IX_Visits_Note ON dbo.Visits (Note) ON psVisitYear (VisitDate);
WITH Nums AS (SELECT TOP (300000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c)
SELECT n INTO #Nums FROM Nums;
INSERT INTO dbo.Cherries SELECT n, 5 FROM #Nums;
INSERT INTO dbo.Pumpkins SELECT n, 'Pumpkin', REPLICATE(CAST('orange ' AS varchar(max)), 6000) FROM #Nums WHERE n <= 300;
INSERT INTO dbo.Visits (VisitID, VisitDate) SELECT n, DATEADD(DAY, n % 1000, '2024-01-01') FROM #Nums WHERE n <= 3000;
DROP TABLE #Nums;

Count Rows and Space Together

The view sys.dm_db_partition_stats has one row for each partition of each index. Two columns matter here. row_count holds the rows, and reserved_page_count holds the pages that the structure owns. Both are counted only for the heap or the clustered index, which are index 0 and 1. A nonclustered index repeats the rows of the table and would count them twice.

The query turns the pages into megabytes, with one page equal to 8 KB. It then adds the bytes per row, and ranks every table twice. The two ranks show at once whether rows or size makes a table huge. The view needs the VIEW DATABASE STATE permission, which is VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later.

WITH Base AS (
    SELECT s.name + N'.' + t.name AS TableName,
           SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS RowCounts,
           SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.reserved_page_count ELSE 0 END) * 8 / 1024.0 AS TableMB
    FROM sys.dm_db_partition_stats AS ps
    JOIN sys.tables AS t ON t.object_id = ps.object_id
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    GROUP BY s.name, t.name
)
SELECT TableName, RowCounts,
       CAST(TableMB AS decimal(12,2)) AS TableMB,
       CAST(TableMB * 1048576 / NULLIF(RowCounts, 0) AS decimal(12,1)) AS BytesPerRow,
       RANK() OVER (ORDER BY RowCounts DESC) AS RowsRank,
       RANK() OVER (ORDER BY TableMB DESC) AS SizeRank
FROM Base
ORDER BY SizeRank;

Cherries ranks first by rows and second by size. Pumpkins ranks third by rows and first by size. A table with 300 rows is more than three times larger than one with 300,000. The bytes per row explain it. A cherry row needs 15 bytes, and a pumpkin row needs about 52 KB. The row count is cheap and the size is what costs disk, backup time and memory.

Visits shows the partitions at work. Its three partitions add up to 3,000 rows. The index on the note column is left out of both numbers. For the space of the indexes, read Size and Rows of Each Table in SQL Server, Indexes Included.

Check the Result With sp_spaceused

The built-in procedure sp_spaceused reports the same space for one table. Compare it with the two numbers above.

EXEC sp_spaceused N'dbo.Pumpkins';
EXEC sp_spaceused N'dbo.Cherries';

Reserved space of 15,504 KB is 15.14 MB, and 4,296 KB is 4.20 MB. Both match the query. The procedure handles one table per call. The query lists the whole database in one pass, so it answers which tables are huge across the whole database.

Why the Common Query Goes Wrong

The older script joins sys.partitions to sys.allocation_units. Two readers proposed fixes for it, SUM and MAX. The next query prints both.

SELECT t.name AS TableName, SUM(p.rows) AS SumRows, MAX(p.rows) AS MaxRows
FROM sys.tables AS t
JOIN sys.indexes AS i ON i.object_id = t.object_id
JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.index_id IN (0, 1)
GROUP BY t.name
ORDER BY t.name;

Pumpkins shows 600 rows under the sum. The long text column adds a second allocation unit. That is the second trap in Size and Rows of Each Table in SQL Server, Indexes Included. Visits shows the opposite problem. The maximum returns 1,098, the largest partition, and misses the other two. Neither fix is right for every table. The partition stats view has one row per partition and no allocation unit, so the sum is correct there.

The column row_count is documented as approximate. That is close enough for a size report. When you need the exact number, use COUNT_BIG.

Is Size Always the Better Number?

You could argue that size is the only number that matters, because disk and memory care about bytes. That holds for storage. It fails for a query, because an operator that touches 300,000 rows costs CPU on a small table too. A report on huge tables needs both numbers, which is why the query keeps both ranks. I look at the size first and the row count second.

What to Remember

Which tables are huge is a question about rows and size, and the two disagree. Count the rows from the heap or the clustered index only. Take the size from the same structure, and rank both. Divide pages by 128 to get megabytes, or multiply by 8 and divide by 1,024. Use decimals, because integer division rounds small tables to zero.

When you finish, run the cleanup script. It removes the demo database.

USE master;
GO
ALTER DATABASE TableSpaceDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TableSpaceDemo;

A big table is not a table with many rows, it is a table that costs a lot to keep.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.