Data Compression

Overview

The Data Compression report answers two questions for one database: which tables and indexes are compressed today, and which ones are worth compressing?

Every heap, clustered, nonclustered and columnstore index on a user table is listed with its current compression, its size, and how much of its work since the last restart was updates and how much was range scans. From that mix the page recommends PAGE, ROW or no change, and the Estimate top 25 button asks SQL Server how much the largest objects would actually shrink.

Nothing on this page changes the database. The side pane shows the ALTER INDEX ... REBUILD script for the selected rows; running it is up to you.

Where to find it

A database-level report. Select a database in the tree, open Real Time, then Data Compression. It is also linked from Table Sizes: with a table selected, the link lands on that table’s rows.

The page is available from SQL Server 2008, when data compression arrived. It is not shown for tempdb.

Reading the chart

The chart shows the 20 largest objects. The main bar is the size now; objects with a recommendation are drawn in the warning color. The thin bar under each one is the size after the recommendation, once it has been estimated, so the gap between the two is the space you would win back. Click a bar to select its row.

The heading gives the count of objects, how many have a recommendation, the total size now and, after an estimate, the total size after the estimated savings.

Reading the grid

Column What it is
Table The table.
Index The index, or (heap).
Recommendation PAGE, ROW, COLUMNSTORE_ARCHIVE, or why nothing is recommended (Already PAGE, None: under 100 MB, None: saves under 10%, None: sparse columns).
Size MB Reserved space for the index, all partitions.
Est. Saving % The saving the estimate found for the recommended setting. Empty until estimated.
Current The compression today, or Mixed (NONE to PAGE) when partitions differ.
Update % Leaf updates as a share of all leaf operations since the restart.
Scan % Range scans as a share of all leaf operations since the restart.
Est. ROW MB / Est. PAGE MB The size with ROW or PAGE, from the estimate.
Partitions, Type, Rows Details of the index.

Hover a row for the full reasoning behind its recommendation.

How the recommendation is made

The rules follow the SQL Server Customer Advisory Team data compression whitepaper:

  • PAGE when updates are under 20% of the operations and range scans over 75%.
  • PAGE when updates are under 5%: data that is written once and rarely changed, or that has not been touched since the restart, pays for PAGE only when a row is written.
  • ROW otherwise: frequent updates make PAGE cost CPU on every change.
  • No change when the object is under 100 MB, when the estimate says the saving is under 10%, when every partition is already at the recommended setting, or when the table has sparse columns (which cannot be compressed).
  • Columnstore is already compressed. From SQL Server 2019 the estimate can include COLUMNSTORE_ARCHIVE, which is only suggested for data that is rarely queried.

The read/write mix comes from sys.dm_db_index_operational_stats, which resets when the instance restarts. Under 7 days of uptime every recommendation is marked low confidence. The same happens when the login does not have VIEW DATABASE STATE, in which case the page still lists every object and says why the mix is missing.

Estimating savings

Estimate top 25 runs sp_estimate_data_compression_savings for the 25 largest objects that could still be compressed further, one at a time, with ROW and PAGE (and COLUMNSTORE_ARCHIVE for columnstore on SQL Server 2019 and later). The procedure copies a sample of each object into tempdb, so it can take minutes on big tables. It only runs when you click the button, shows its progress, and Cancel stops it; the objects already estimated are kept. Estimates also survive a Refresh.

Editions

Before SQL Server 2016 SP1, data compression needs Enterprise (or Developer or Evaluation) edition. On Standard edition before that the page lists the objects and their compression, and says Compression requires Enterprise edition on this version; the Estimate button is disabled.

The script adds ONLINE = ON on Enterprise class editions so the rebuild does not block the table.

Right-click actions

Item What it does
Show Script Opens the side pane with the rebuild script for the selected rows.
Estimate top 25 The same as the toolbar button.
Copy the recommendations as text Every object with a recommendation and the reason.

The grid also has the usual Go to menu, CSV and Excel export.

For an estate-wide compression plan across every database and instance, use Space Recovery.

Report Why you would go there
Table Sizes The size of the table and where it goes.
Table Space Breakdown Rows, index, off-row content and slack for the table.
Partitioned Tables Compression can be set per partition.
Most Used Indexes How the index is used.
Files A rebuild needs free space in the data file and writes to the log.