Browse cheat sheets · SQL Server
Check SQL Server Table Sizes
Measure SQL Server table row counts and storage usage, separate data from index space, and identify which tables are driving database growth.
On this page
- Problem
- Quick answer
- Code / commands
- 1. List tables by reserved space
- 2. Separate table data from index space
- 3. Show index sizes for one table
- 4. Show partition-level size
- 5. Show largest tables by row count
- 6. Compare used versus reserved space
- What the result means
- What to look for
- A small number of tables dominate the database
- Index space is close to or larger than base data space
- Row count is high but storage is relatively modest
- Storage is high but row count is modest
- One partition contains most of the rows
- Large unused reserved space
- Production notes
- sys.dm_db_partition_stats is metadata-based and lightweight
- Table size is not database file size
- Compression changes the relationship between rows and storage
- LOB data matters
- Be careful with automated cleanup
- Common mistakes
- 1. Counting rows once per index
- 2. Treating reserved space as fully used data
- 3. Dropping large indexes based only on size
- 4. Shrinking files because a table was cleaned up
- 5. Comparing tables only by row count
- 6. Ignoring partition distribution
- Next step
- Related deep dives
Problem
A SQL Server database is growing, storage is under pressure, a maintenance operation is taking longer than expected, or you need to identify which tables consume the most space.
The goal is to answer:
- Which tables are the largest?
- How many rows do they contain?
- How much space is used by data versus indexes?
- Which objects are driving database growth?
- Is a large table expected, or does its size suggest retention, indexing, or data-model issues?
Quick answer
For a reliable, lightweight inventory of row counts and allocated space, aggregate sys.dm_db_partition_stats by table.
SELECT
s.name AS schema_name,
t.name AS table_name,
SUM(ps.row_count) AS row_count,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
CAST(
SUM(
CASE
WHEN ps.index_id IN (0, 1)
THEN ps.in_row_data_page_count
+ ps.lob_used_page_count
+ ps.row_overflow_used_page_count
ELSE 0
END
) * 8.0 / 1024
AS decimal(18,2)
) AS data_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE
t.is_ms_shipped = 0
GROUP BY
s.name,
t.name
ORDER BY
reserved_mb DESC;
Use this as the first pass when you need to understand where database space is going.
Code / commands
1. List tables by reserved space
Use this for a quick ranked inventory.
SELECT
s.name AS schema_name,
t.name AS table_name,
SUM(ps.row_count) AS row_count,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE
t.is_ms_shipped = 0
GROUP BY
s.name,
t.name
ORDER BY
reserved_mb DESC;
reserved_mb is usually the most useful first sort because it shows how much space SQL Server has allocated to the object.
2. Separate table data from index space
This is more useful when a large table may actually be large because of indexes.
WITH table_space AS
(
SELECT
ps.object_id,
SUM(ps.row_count) AS row_count,
SUM(
CASE
WHEN ps.index_id IN (0, 1)
THEN ps.in_row_data_page_count
+ ps.lob_used_page_count
+ ps.row_overflow_used_page_count
ELSE 0
END
) AS data_pages,
SUM(
CASE
WHEN ps.index_id > 1
THEN ps.used_page_count
ELSE 0
END
) AS nonclustered_index_pages,
SUM(ps.reserved_page_count) AS reserved_pages,
SUM(ps.used_page_count) AS used_pages
FROM sys.dm_db_partition_stats AS ps
GROUP BY
ps.object_id
)
SELECT
s.name AS schema_name,
t.name AS table_name,
ts.row_count,
CAST(ts.data_pages * 8.0 / 1024 AS decimal(18,2)) AS data_mb,
CAST(ts.nonclustered_index_pages * 8.0 / 1024 AS decimal(18,2)) AS nonclustered_index_mb,
CAST(ts.used_pages * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
CAST(ts.reserved_pages * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM table_space AS ts
INNER JOIN sys.tables AS t
ON t.object_id = ts.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE
t.is_ms_shipped = 0
ORDER BY
reserved_mb DESC;
Use this when you need to know whether storage is dominated by the base table or by secondary indexes.
3. Show index sizes for one table
Once you find a large table, drill into its indexes.
DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name sysname = N'YourTable';
SELECT
i.index_id,
i.name AS index_name,
i.type_desc,
SUM(ps.row_count) AS row_count,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ps
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE
i.object_id = OBJECT_ID(QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name))
GROUP BY
i.index_id,
i.name,
i.type_desc
ORDER BY
reserved_mb DESC;
This is useful when:
- index maintenance is slow
- backups are growing
- write cost is high
- you suspect duplicate or oversized indexes
Do not drop an index solely because it is large. First verify whether the workload uses it.
4. Show partition-level size
For partitioned tables, table-level totals can hide where the data actually lives.
DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name sysname = N'YourPartitionedTable';
SELECT
ps.partition_number,
SUM(ps.row_count) AS row_count,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM sys.dm_db_partition_stats AS ps
WHERE
ps.object_id = OBJECT_ID(QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name))
GROUP BY
ps.partition_number
ORDER BY
ps.partition_number;
Use this to check whether data is distributed across partitions as expected.
A heavily skewed partitioning strategy can undermine both performance and maintenance goals.
5. Show largest tables by row count
Space and row count answer different questions.
SELECT
s.name AS schema_name,
t.name AS table_name,
SUM(ps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE
t.is_ms_shipped = 0
AND ps.index_id IN (0, 1)
GROUP BY
s.name,
t.name
ORDER BY
row_count DESC;
Filter to heap/clustered rows (index_id 0 or 1) so you do not count the same table rows once per nonclustered index.
6. Compare used versus reserved space
A large gap between reserved and used space can be worth investigating.
SELECT
s.name AS schema_name,
t.name AS table_name,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
CAST(
(SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8.0 / 1024
AS decimal(18,2)
) AS unused_reserved_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE
t.is_ms_shipped = 0
GROUP BY
s.name,
t.name
ORDER BY
unused_reserved_mb DESC;
Do not immediately shrink the database because an object has unused reserved pages. Understand the growth pattern first.
What the result means
| Field | Why it matters |
|---|---|
row_count |
Approximate current row count from partition metadata. Useful for capacity and scale comparisons. |
reserved_mb |
Space allocated to the object, including currently unused allocated pages. |
used_mb |
Portion of reserved space currently used. |
data_mb |
Space used by heap/clustered table data, including LOB and row-overflow pages in the examples above. |
nonclustered_index_mb |
Space used by nonclustered indexes. |
unused_reserved_mb |
Allocated but not currently used space. |
partition_number |
Lets you inspect distribution across table partitions. |
index_id |
Identifies heap, clustered, and nonclustered index structures. |
What to look for
A small number of tables dominate the database
This usually means capacity planning should focus on those objects first.
Investigate:
- retention policy
- growth rate
- archive strategy
- partitioning
- compression
- index footprint
Index space is close to or larger than base data space
This is not automatically bad.
Wide tables and read-heavy workloads can legitimately require significant index space.
But it is a good trigger to review:
- unused indexes
- overlapping indexes
- redundant INCLUDE columns
- duplicate access paths
- write amplification
Row count is high but storage is relatively modest
Possible causes include:
- narrow rows
- compression
- low LOB usage
- efficient schema design
Large row count does not automatically mean storage pressure.
Storage is high but row count is modest
Look for:
varchar(max)/nvarchar(max)- XML
- large binary data
- row-overflow data
- wide indexes
- sparse or wide schemas
One partition contains most of the rows
Possible interpretations:
- expected current-period concentration
- partition key skew
- historical partitions not being managed as intended
- partitioning strategy not matching access patterns
Large unused reserved space
Possible reasons include:
- recent deletes
- index rebuild activity
- allocation patterns
- object growth history
Do not treat unused reserved space as a mandate to shrink files.
Production notes
sys.dm_db_partition_stats is metadata-based and lightweight
For table-size inventory, it is generally preferable to scanning the table or using COUNT(*) against large production objects.
The row counts are suitable for operational diagnostics and capacity work, but they are not the same as running an exact transactional COUNT(*) under your workload’s isolation semantics.
Table size is not database file size
Object allocation answers:
Which objects consume database pages?
It does not answer:
- how much free space remains inside the data file
- how much disk space the database files occupy
- transaction log size
- backup size
Those require separate file/database-level queries.
Compression changes the relationship between rows and storage
Row/page compression and columnstore can make raw row counts difficult to compare directly across tables.
Use actual allocated pages, not row counts alone, for storage planning.
LOB data matters
Large-value data can consume substantial out-of-row storage.
A table may look small by row count but large in pages because of:
varchar(max)nvarchar(max)varbinary(max)- XML
- other LOB allocations
Be careful with automated cleanup
Large tables often sit at the center of business processes.
Before deleting/archive/purging data, validate:
- retention requirements
- foreign keys
- downstream reporting
- replication/CDC implications
- backup/recovery expectations
- batch-log impact
Common mistakes
1. Counting rows once per index
If you sum row_count across all index IDs, you can overstate table rows because nonclustered indexes also contain rows.
For table row counts, use heap/clustered structures (index_id IN (0,1)), or structure your aggregation carefully.
2. Treating reserved space as fully used data
Reserved pages include allocated-but-unused pages.
Compare reserved_page_count with used_page_count.
3. Dropping large indexes based only on size
A large index can still be critical.
Check index usage and query plans before removal.
4. Shrinking files because a table was cleaned up
Frequent shrink/grow cycles can create fragmentation and operational churn.
Reclaim file space only when there is a clear capacity reason and a deliberate plan.
5. Comparing tables only by row count
Rows vary dramatically in width and storage characteristics.
Always pair row count with actual page usage.
6. Ignoring partition distribution
A partitioned table can be large for legitimate reasons while still being poorly distributed.
Inspect partition-level row and page counts before assuming the partitioning strategy is working.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.