Apply Row and Page Compression to Tables and Indexes
Heaps and indexes that were never compressed keep occupying full-size pages, and nothing in a default database flags them. sys.dm_db_index_physical_stats joined to sys.partitions lists the uncompressed heaps and indexes above a size threshold, and the script below turns that list into ready-to-review ALTER INDEX ... REBUILD and ALTER TABLE ... REBUILD statements, largest objects first.
Purpose and Overview
SQL Server 2008 documented row and page compression as available only in the Enterprise and Developer editions. The SQL Server 2016 editions page now lists data compression as supported in Enterprise, Standard, Web, Express with Advanced Services, and Express, and footnotes that row as applying to SQL Server 2016 SP1 as part of a common programmability surface area across editions. Since that change, compression is an option on Standard and Web instances too, so finding the objects that are still uncompressed is a routine maintenance question rather than an Enterprise-only one.
The difficulty is knowing which objects to look at. A production database can hold many indexes across many tables, a mix of heaps and clustered indexes, and partitioned objects, and no single catalog view shows size and compression state together. sys.dm_db_index_physical_stats reports the size of each index and heap through its page_count column. sys.partitions carries data_compression_desc, which Microsoft describes as the state of compression for each partition: NONE, ROW, or PAGE for rowstore tables, and COLUMNSTORE or COLUMNSTORE_ARCHIVE for columnstore tables. Joining the two on object, index, and partition number gives both facts in one row.
The script returns one row per qualifying partition with the schema, table, index name (or HEAP), index type, partition number, page count, fragmentation percentage, current compression, and a generated compression_command column. Both branches of the generated command hard-code DATA_COMPRESSION = ROW; switching a row to PAGE is a text edit before execution. Only user tables with more than 100 pages in their in-row allocation unit and a current state of NONE are returned, so small objects and anything already compressed do not appear.
The function takes five arguments: database ID, object ID, index ID, partition number, and scan mode. Microsoft documents NULL for the object, index, and partition arguments as returning all of them, and the valid modes are LIMITED, SAMPLED, and DETAILED. The default when the mode is omitted or NULL is LIMITED, which Microsoft calls the fastest mode, scanning the smallest number of pages.
Code Breakdown
The script runs in the context of the target database, calls sys.dm_db_index_physical_stats in LIMITED mode, and joins to sys.indexes, sys.objects, sys.schemas, and sys.partitions. It produces a diagnostic result set and the ALTER statements in one query.
1USE [YourDatabase];
2GO
3
4SELECT
5 s.name AS schema_name,
6 o.name AS table_name,
7 CASE i.index_id
8 WHEN 0 THEN 'HEAP'
9 ELSE i.name
10 END AS index_name,
11 i.index_id,
12 i.type_desc AS index_type,
13 p.partition_number,
14 ps.page_count,
15 CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,1))
16 AS frag_pct,
17 p.data_compression_desc AS current_compression,
18 CASE
19 WHEN i.index_id = 0
20 -- Heap: uses ALTER TABLE syntax
21 THEN 'ALTER TABLE [' + s.name + '].[' + o.name + ']'
22 + ' REBUILD WITH (DATA_COMPRESSION = ROW);'
23 ELSE
24 -- Clustered and non-clustered indexes
25 'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + ']'
26 + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);'
27 END AS compression_command
28FROM sys.dm_db_index_physical_stats(
29 DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
30INNER JOIN sys.indexes AS i
31 ON ps.object_id = i.object_id
32 AND ps.index_id = i.index_id
33INNER JOIN sys.objects AS o
34 ON i.object_id = o.object_id
35INNER JOIN sys.schemas AS s
36 ON o.schema_id = s.schema_id
37INNER JOIN sys.partitions AS p
38 ON ps.object_id = p.object_id
39 AND ps.index_id = p.index_id
40 AND ps.partition_number = p.partition_number
41WHERE o.type_desc = 'USER_TABLE'
42 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
43 AND ps.page_count > 100
44 AND p.data_compression_desc = 'NONE'
45ORDER BY ps.page_count DESC;
The query above adds one predicate, ps.alloc_unit_type_desc = 'IN_ROW_DATA', to the original script. Without it, a table with LOB or row-overflow data returns an additional row per index and partition for those allocation units, and each extra row produces a duplicate ALTER statement.
Calling sys.dm_db_index_physical_stats
sys.dm_db_index_physical_stats is a table-valued function, so it needs parentheses and arguments. DB_ID() scopes the call to the current database after the USE statement. Passing NULL for object ID, index ID, and partition number returns every object, index, and partition. Microsoft warns that DB_ID and OBJECT_ID return NULL for names that do not exist and that the function treats NULL as a wildcard, so a mistyped name silently widens the scan; this script passes NULL deliberately.
The fifth argument is 'LIMITED'. According to the documentation, in LIMITED mode an index has only its parent-level B-tree pages (the pages above the leaf level) scanned, and for a heap the PFS and IAM pages are examined and the data pages are scanned. It is therefore lighter than the other modes but not a metadata-only read. LIMITED also leaves compressed_page_count as NULL, which this script does not use. The same page's own rebuild-or-reorganize example calls the function in LIMITED mode and filters on avg_fragmentation_in_percent, so the frag_pct column here is populated. The documented exception is that fragmentation is NULL for heaps in SAMPLED mode.
Joining to sys.indexes, sys.objects, and sys.schemas
sys.indexes resolves each (object_id, index_id) pair to an index name and type description. An index_id of 0 is a heap and has no index name, so the CASE expression substitutes the literal 'HEAP'. sys.objects supplies the table name and type_desc, filtered to 'USER_TABLE' to leave out views and system objects. sys.schemas supplies the schema name that the generated statements need.
Joining to sys.partitions for current compression state
Microsoft documents sys.partitions as containing a row for each partition of all the tables and most types of indexes in the database, and states that every table and index has at least one partition whether or not it is explicitly partitioned. For non-partitioned objects the partition_number value is 1. The join on (object_id, index_id, partition_number) therefore matches each function row to exactly one catalog row. The data_compression_desc = 'NONE' predicate removes partitions that are already ROW or PAGE compressed, and also removes columnstore objects, which report COLUMNSTORE or COLUMNSTORE_ARCHIVE.
Generating ALTER statements for heaps and indexes
A heap has no index name, so the script emits ALTER TABLE [schema].[table] REBUILD WITH (DATA_COMPRESSION = ROW);. Microsoft's data compression page describes ALTER TABLE ... REBUILD as the way to rebuild a table, and lists a whole table stored as a heap among the objects that can be compressed. For named indexes it emits ALTER INDEX [name] ON [schema].[table] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);. The ALTER INDEX page states that PARTITION = ALL rebuilds all partitions. Its examples for this clause use partitioned indexes, and it does not say what happens when the clause is used on a non-partitioned index, so run a few generated statements on a test copy before executing a whole batch. Because a nonclustered index does not inherit its table's compression setting, each index appears as its own row with its own statement.
Filtering by page count
The ps.page_count > 100 predicate drops small objects. One hundred 8 KB pages is about 800 KB; the number is a convenience cutoff chosen for this script, not a Microsoft threshold. Raise it to restrict a first pass to the largest objects, or lower it to see everything. Note that the result is not a complete inventory while the filter is in place: any partition of 100 pages or fewer is excluded.
Key Benefits and Use Cases
- Database-wide inventory in one query — one result set shows every user-table heap and index above the page threshold, with its compression state, without looping object by object.
- Reviewable output — the generated statements are text in a result column, so a DBA can read each one, change
ROWtoPAGE, or discard it before running anything. - Size-ordered priority —
ORDER BY ps.page_count DESCputs the largest candidates first, so a limited maintenance window can be spent on the biggest objects. - Heap and index syntax handled together — the
CASEexpression emitsALTER TABLEfor heaps andALTER INDEXfor indexes, so one query covers both object types. - Current state visible —
data_compression_descshows what each partition already uses, so objects compressed in an earlier pass are filtered out. - Works on Standard, Web, and Express from SQL Server 2016 SP1 — the 2016 editions page lists data compression for all of those editions, so the statements apply across a mixed-edition estate on that release or later.
Performance Considerations
- Scan cost depends on mode: Microsoft states the modes are progressively slower from
LIMITEDtoDETAILED. If you switch toDETAILED, pass a specificobject_idinstead ofNULLso the function does not scan every object. SAMPLEDis not always a sample: the page describesSAMPLEDas based on a 1 percent sample of the pages in the index or heap, and saysDETAILEDis used instead when the index or heap has fewer than 10,000 pages. It also leaves fragmentationNULLfor heaps.- Rebuilds hold locks: an offline rebuild of a clustered or nonclustered index takes a schema modification (
Sch-M) lock on the table, which Microsoft says prevents all user access to the table for the duration of the operation.ONLINE = ONholds only brief locks at the start and end, but the 2016 editions page lists online indexing as available in Enterprise only (and Developer and Evaluation, which follow Enterprise), so on Standard and Web the rebuilds run offline. - Logging depends on the recovery model: the
ALTER INDEXpage says a rebuild can be minimally logged if the database recovery model is bulk-logged or simple. Under the full recovery model, size the log and backups for the rebuild. - Heap changes rebuild other indexes: the data compression page says changing a heap's compression setting requires all nonclustered indexes on the table to be rebuilt so they point to the new row locations.
- Disk space: the same page states the space needed to enable or disable row or page compression is the same as for creating or rebuilding an index. For partitioned data it can be reduced by working one partition at a time.
Practical Tips
- Estimate first — run
sys.sp_estimate_data_compression_savingson the largest candidates. It samples the object intotempdb, applies the requested compression, and reports the estimated size.@data_compressiontakes one value per call, so run it once forROWand once forPAGEand compare. Microsoft's example isEXECUTE sys.sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';. - Expect some statements to fail — the data compression page notes a table cannot be enabled for compression when the maximum row size plus compression overhead exceeds 8,060 bytes, and that off-row data is not compressed. An error on one generated statement does not mean the others are wrong.
- Treat heaps separately from indexes — pages added to a heap by ordinary DML do not receive page compression until the heap is rebuilt, according to the data compression page. The DMV's
compressed_page_countcolumn, populated inSAMPLEDandDETAILEDmode, counts compressed pages, and Microsoft suggests rebuilding a heap when that count grows larger than the threshold you want. - Work partition by partition when windows are short — replace
PARTITION = ALLwith a specific partition number on a partitioned index, as theALTER INDEXpage describes, to spread rebuilds across several windows. - Confirm afterwards in the catalog — after a pass, query
sys.partitionsfordata_compression_descto confirm the new state:SELECT OBJECT_NAME(object_id), index_id, partition_number, data_compression_desc FROM sys.partitions WHERE data_compression_desc <> 'NONE';. That query is not from Microsoft's documentation, but it reads only the catalog view. - Re-run on a schedule — the script has no state, so a SQL Agent job that writes its output to a table will surface newly created tables and indexes on later runs.
Conclusion
sys.dm_db_index_physical_stats joined to sys.partitions gives one query that lists uncompressed heaps and indexes above a size cutoff, with the rebuild statement for each already written. It fits between the estimate step (sys.sp_estimate_data_compression_savings) and the rebuild step in a DBA's compression routine, and it leaves the choice of ROW or PAGE, and which statements to run, with the person reading the output.
References
- sys.dm_db_index_physical_stats (Transact-SQL) — Microsoft Learn — Arguments, the LIMITED, SAMPLED, and DETAILED scan modes, the rows returned per allocation unit, and the
page_countandavg_fragmentation_in_percentcolumns. - Data compression — Microsoft Learn — Row and page compression, the objects that can be compressed, and considerations for heaps, nonclustered indexes, and partitions.
- ALTER INDEX (Transact-SQL) — Microsoft Learn — REBUILD with
DATA_COMPRESSION,PARTITION = ALL, minimal logging by recovery model, and the locks taken by online and offline rebuilds. - sys.partitions (Transact-SQL) — Microsoft Learn — Column reference for
data_compressionanddata_compression_desc,index_idvalues, andpartition_number. - sys.sp_estimate_data_compression_savings (Transact-SQL) — Microsoft Learn — How the procedure samples an object into
tempdband estimates its size under the requested compression setting. - Editions and Supported Features - SQL Server 2016 — Microsoft Learn — The edition table listing data compression, with the SQL Server 2016 SP1 footnote, and online indexing by edition.
Posts in this series
- Generate DBCC SHOWCONTIG Commands for All SQL Server Tables
- DBCC DBREINDEX: SQL Server Index Rebuild Script
- SQL Server DBCC DBREINDEX with Timing: Index Maintenance
- SQL Server Index Fragmentation: dm_db_index_physical_stats
- SQL Server DBCC CHECKDB Last Run Date Report
- SQL Server VLF Count Report: Virtual Log File Analysis
- SQL Server Duplicate and Overlapping Index Detection Script
- DBCC DBREINDEX vs ALTER INDEX REBUILD in SQL Server
- SQL Server DBCC SHRINKDATABASE: When and When Not to Shrink
- SQL Server DBCC CHECKDB Guide and Repair Options
- SQL Server DBCC Commands Guide
- How to Update Statistics in SQL Server
- Apply Row and Page Compression to Tables and Indexes
- Detect Suspect Database Pages with msdb.dbo.suspect_pages