sys.dm_db_partition_stats - Usage & Examples

SQL Server's storage engine tracks space utilization at the partition level through allocation unit metadata stored in system tables. The sys.dm_db_partition_stats DMV exposes this metadata by joining internal allocation structures with partition information from sys.partitions and sys.allocation_units.

Each row represents a single allocation unit within a partition. For heap tables, you get one row per partition showing IN_ROW_DATA allocation units. For clustered indexes, you see IN_ROW_DATA allocation units plus separate rows for LOB_DATA and ROW_OVERFLOW_DATA when large objects exist. Nonclustered indexes create additional rows for their respective allocation units.

The storage engine maintains these statistics in real-time as DML operations occur. Page splits, row movements, and allocation changes immediately update the underlying metadata. SQL Server 2016 introduced compressed page estimates that improved accuracy for compressed tables. SQL Server 2019 added better handling of columnstore partition statistics, particularly for delta stores.

The reserved_page_count includes allocated but unused pages within extents, which explains why reserved space often exceeds used space. This occurs due to SQL Server's extent-based allocation model where 8 contiguous pages form the smallest allocation unit for multi-page objects.

-- Identify largest tables by row count and space usage
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS table_name,
    i.name AS index_name,
    p.partition_number,
    ps.row_count,
    ps.reserved_page_count * 8 / 1024 AS reserved_mb,
    ps.used_page_count * 8 / 1024 AS used_mb,
    ps.in_row_data_page_count * 8 / 1024 AS data_mb
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY ps.reserved_page_count DESC;

-- Find tables with significant space waste (reserved vs used)
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS table_name,
    SUM(ps.reserved_page_count * 8) / 1024 AS reserved_mb,
    SUM(ps.used_page_count * 8) / 1024 AS used_mb,
    SUM((ps.reserved_page_count - ps.used_page_count) * 8) / 1024 AS waste_mb,
    CAST(SUM(ps.reserved_page_count - ps.used_page_count) * 100.0 / NULLIF(SUM(ps.reserved_page_count), 0) AS DECIMAL(5,2)) AS waste_pct
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
GROUP BY p.object_id
HAVING SUM((ps.reserved_page_count - ps.used_page_count) * 8) / 1024 > 100
ORDER BY waste_mb DESC;

-- Analyze partition distribution for partitioned tables
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS table_name,
    p.partition_number,
    ps.row_count,
    ps.reserved_page_count * 8 / 1024 AS reserved_mb,
    CASE WHEN LAG(ps.row_count) OVER (PARTITION BY p.object_id ORDER BY p.partition_number) IS NULL 
         THEN 0
         ELSE ABS(ps.row_count - LAG(ps.row_count) OVER (PARTITION BY p.object_id ORDER BY p.partition_number)) * 100 / 
              NULLIF(LAG(ps.row_count) OVER (PARTITION BY p.object_id ORDER BY p.partition_number), 0)
    END AS row_variance_pct
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.index_id IN (0,1) -- Heap or clustered index only
  AND EXISTS (SELECT 1 FROM sys.partitions p2 WHERE p2.object_id = p.object_id GROUP BY p2.object_id HAVING COUNT(*) > 1)
ORDER BY schema_name, table_name, partition_number;

-- Identify fragmentation correlation with space usage
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS table_name,
    i.name AS index_name,
    ps.row_count,
    ps.reserved_page_count * 8 / 1024 AS reserved_mb,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    CASE WHEN ps.reserved_page_count > 0 
         THEN CAST(ips.page_count * 100.0 / ps.reserved_page_count AS DECIMAL(5,2))
         ELSE 0 
    END AS page_efficiency_pct
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), p.object_id, p.index_id, p.partition_number, 'LIMITED') ips
WHERE OBJECT_SCHEMA_NAME(p.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
  AND ps.row_count > 1000
  AND i.index_id IN (0,1)
ORDER BY ips.avg_fragmentation_in_percent DESC;

Reclaim wasted space through index reorganization Reorganizes indexes with significant space waste while preserving statistics and minimizing blocking.

-- Generate reorganize commands for indexes with space waste
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    'ALTER INDEX ' + QUOTENAME(i.name) + 
    ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' + QUOTENAME(OBJECT_NAME(p.object_id)) + 
    ' REORGANIZE' + 
    CASE WHEN p.partition_number > 1 THEN ' PARTITION = ' + CAST(p.partition_number AS VARCHAR(10)) ELSE '' END +
    ' WITH (COMPRESS_ALL_ROW_GROUPS = OFF);' + CHAR(13)
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
  AND i.index_id > 0 -- Skip heaps
  AND (ps.reserved_page_count - ps.used_page_count) * 8 / 1024 > 50 -- More than 50MB waste
  AND ps.reserved_page_count > 0
  AND CAST((ps.reserved_page_count - ps.used_page_count) * 100.0 / ps.reserved_page_count AS DECIMAL(5,2)) > 15; -- More than 15% waste

PRINT @sql;
-- Uncomment to execute: EXEC sp_executesql @sql;

Shrink specific data files based on actual usage Calculates optimal shrink targets based on actual space utilization plus 20% buffer.

-- Calculate and execute targeted file shrink operations
DECLARE @target_size_mb INT;
DECLARE @current_size_mb INT;
DECLARE @file_name NVARCHAR(128);

SELECT 
    @target_size_mb = CEILING((SUM(ps.used_page_count) * 8 / 1024) * 1.2), -- 20% buffer
    @current_size_mb = (SELECT size * 8 / 1024 FROM sys.database_files WHERE type = 0 AND file_id = 1),
    @file_name = (SELECT name FROM sys.database_files WHERE type = 0 AND file_id = 1)
FROM sys.dm_db_partition_stats ps;

IF @current_size_mb > @target_size_mb + 500 -- Only shrink if saving more than 500MB
BEGIN
    DECLARE @shrink_sql NVARCHAR(500) = 
        'DBCC SHRINKFILE (''' + @file_name + ''', ' + CAST(@target_size_mb AS VARCHAR(10)) + ') WITH NO_INFOMSGS';
    
    PRINT 'Current size: ' + CAST(@current_size_mb AS VARCHAR(10)) + 'MB';
    PRINT 'Target size: ' + CAST(@target_size_mb AS VARCHAR(10)) + 'MB';
    PRINT 'Command: ' + @shrink_sql;
    
    -- WARNING: Test in dev first, can cause severe fragmentation
    -- EXEC sp_executesql @shrink_sql;
END
ELSE
BEGIN
    PRINT 'Shrink not recommended. Current: ' + CAST(@current_size_mb AS VARCHAR(10)) + 
          'MB, Target: ' + CAST(@target_size_mb AS VARCHAR(10)) + 'MB';
END

Create monitoring alerts for space growth Sets up automated monitoring for tables exceeding space thresholds.

-- Create stored procedure for ongoing space monitoring
CREATE OR ALTER PROCEDURE dbo.usp_MonitorTableSpaceGrowth
    @threshold_mb INT = 1000,
    @growth_threshold_pct DECIMAL(5,2) = 25.0
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Create temp table to store baseline if not exists
    IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'table_space_baseline' AND type = 'U')
    BEGIN
        CREATE TABLE dbo.table_space_baseline (
            object_id INT,
            baseline_date DATETIME2,
            reserved_mb INT,
            row_count BIGINT,
            PRIMARY KEY (object_id)
        );
    END;
    
    -- Insert current snapshot
    WITH current_stats AS (
        SELECT 
            p.object_id,
            SUM(ps.reserved_page_count * 8 / 1024) AS reserved_mb,
            SUM(ps.row_count) AS row_count
        FROM sys.dm_db_partition_stats ps
        JOIN sys.partitions p ON ps.partition_id = p.partition_id
        WHERE OBJECT_SCHEMA_NAME(p.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
        GROUP BY p.object_id
    )
    MERGE dbo.table_space_baseline AS target
    USING current_stats AS source ON target.object_id = source.object_id
    WHEN MATCHED THEN 
        UPDATE SET 
            baseline_date = GETDATE(),
            reserved_mb = source.reserved_mb,
            row_count = source.row_count
    WHEN NOT MATCHED THEN
        INSERT (object_id, baseline_date, reserved_mb, row_count)
        VALUES (source.object_id, GETDATE(), source.reserved_mb, source.row_count);
    
    -- Report significant growth
    SELECT 
        OBJECT_SCHEMA_NAME(cs.object_id) AS schema_name,
        OBJECT_NAME(cs.object_id) AS table_name,
        b.reserved_mb AS baseline_mb,
        cs.reserved_mb AS current_mb,
        cs.reserved_mb - b.reserved_mb AS growth_mb,
        CAST((cs.reserved_mb - b.reserved_mb) * 100.0 / NULLIF(b.reserved_mb, 0) AS DECIMAL(5,2)) AS growth_pct
    FROM (
        SELECT 
            p.object_id,
            SUM(ps.reserved_page_count * 8 / 1024) AS reserved_mb
        FROM sys.dm_db_partition_stats ps
        JOIN sys.partitions p ON ps.partition_id = p.partition_id
        GROUP BY p.object_id
    ) cs
    JOIN dbo.table_space_baseline b ON cs.object_id = b.object_id
    WHERE cs.reserved_mb > @threshold_mb
      AND (cs.reserved_mb - b.reserved_mb) * 100.0 / NULLIF(b.reserved_mb, 0) > @growth_threshold_pct
    ORDER BY growth_mb DESC;
END;

Configure automatic space monitoring using SQL Agent jobs that execute custom procedures querying sys.dm_db_partition_stats every 6 hours. Set alerts when tables grow beyond 25% of their baseline size within a 24-hour period.

Implement proactive index maintenance using Enterprise Edition's online rebuild capabilities for tables identified through partition stats as having significant space waste. Schedule these operations during maintenance windows based on space utilization patterns observed through historical DMV data.

Design initial table sizing using sys.dm_db_partition_stats data from similar production workloads. Calculate growth factors from existing partition statistics to set appropriate initial file sizes and autogrowth increments, preventing excessive space allocation.

Establish partition pruning strategies for temporal data by monitoring row count and space distribution across partitions. Use sys.dm_db_partition_stats to identify partition skew and implement partition elimination techniques that maintain query performance while controlling storage growth.

Configure database-level monitoring that compares sys.dm_db_partition_stats aggregated values against file-level space allocation from sys.database_files. This prevents scenarios where individual table growth goes unnoticed until database-level space alerts trigger.