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 ROW to PAGE, or discard it before running anything.
  • Size-ordered priority — ORDER BY ps.page_count DESC puts the largest candidates first, so a limited maintenance window can be spent on the biggest objects.
  • Heap and index syntax handled together — the CASE expression emits ALTER TABLE for heaps and ALTER INDEX for indexes, so one query covers both object types.
  • Current state visible — data_compression_desc shows 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 LIMITED to DETAILED. If you switch to DETAILED, pass a specific object_id instead of NULL so the function does not scan every object.
  • SAMPLED is not always a sample: the page describes SAMPLED as based on a 1 percent sample of the pages in the index or heap, and says DETAILED is used instead when the index or heap has fewer than 10,000 pages. It also leaves fragmentation NULL for 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 = ON holds 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 INDEX page 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_savings on the largest candidates. It samples the object into tempdb, applies the requested compression, and reports the estimated size. @data_compression takes one value per call, so run it once for ROW and once for PAGE and compare. Microsoft's example is EXECUTE 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_count column, populated in SAMPLED and DETAILED mode, 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 = ALL with a specific partition number on a partitioned index, as the ALTER INDEX page describes, to spread rebuilds across several windows.
  • Confirm afterwards in the catalog — after a pass, query sys.partitions for data_compression_desc to 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

Posts in this series