Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: Storage & CapacityDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. List tables by reserved space
  5. 2. Separate table data from index space
  6. 3. Show index sizes for one table
  7. 4. Show partition-level size
  8. 5. Show largest tables by row count
  9. 6. Compare used versus reserved space
  10. What the result means
  11. What to look for
  12. A small number of tables dominate the database
  13. Index space is close to or larger than base data space
  14. Row count is high but storage is relatively modest
  15. Storage is high but row count is modest
  16. One partition contains most of the rows
  17. Large unused reserved space
  18. Production notes
  19. sys.dm_db_partition_stats is metadata-based and lightweight
  20. Table size is not database file size
  21. Compression changes the relationship between rows and storage
  22. LOB data matters
  23. Be careful with automated cleanup
  24. Common mistakes
  25. 1. Counting rows once per index
  26. 2. Treating reserved space as fully used data
  27. 3. Dropping large indexes based only on size
  28. 4. Shrinking files because a table was cleaned up
  29. 5. Comparing tables only by row count
  30. 6. Ignoring partition distribution
  31. Next step
  32. 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.

Next step

Use the size profile to decide where to investigate next.

  • One table dominates storage → inspect retention, growth rate, schema width, LOB usage, and partitioning.
  • Indexes dominate space → review index usage, duplication, and write overhead.
  • One partition dominates → validate partition key choice and lifecycle management.
  • Large unused reserved space → understand object growth/deletion history before considering reclamation.
  • Table is growing rapidly → add trend monitoring rather than relying on a one-time snapshot.
  • Database file is still much larger than table totals → inspect free space, internal objects, and file-level allocation.
  • Storage is fine but performance is poor → return to workload, wait, query, and index diagnostics instead of capacity analysis.

← All cheat sheets