SQL Server Compression and Partitioning: When to Use Each, When to Use Both, and How to Decide

Two of the most effective tools for managing large SQL Server tables are data compression and table partitioning. Both reduce the impact of large tables on query performance and maintenance operations. Both are frequently misapplied. And they are often discussed as alternatives to each other when the right answer is frequently to use both, in the right order, for the right reasons.

This article covers what each one actually does at the storage layer, when each one is appropriate, when each one is the wrong choice, how to test before committing, and how the SQLYARD Compression and Partitioning Advisor gives you a data-driven starting point for both decisions in any database. Everything here applies to SQL Server 2016 through SQL Server 2025.

1 What Data Compression Actually Does Beginner

Data compression in SQL Server works at the page level. SQL Server stores data in 8KB pages. Compression reduces the number of pages required to store the same data, which means SQL Server reads fewer pages from disk to satisfy a query. On I/O-intensive workloads that read large amounts of data this translates directly to faster queries. The tradeoff is that every read and write operation now requires CPU cycles to compress and decompress data on the fly.

SQL Server 2016 and later offers four compression types:

2 What Table Partitioning Actually Does Beginner

Table partitioning divides a large table into smaller physical units called partitions based on a partition key column, almost always a date or datetime column. From the application’s perspective the table is one object and all existing queries continue to work unchanged. Internally SQL Server stores each partition separately and can process them independently.

The primary benefit of partitioning is partition elimination: when a query includes a filter on the partition key column, SQL Server can skip entire partitions that cannot contain matching rows. A query for orders from January 2026 on a table partitioned monthly touches only the January 2026 partition rather than scanning the entire multi-year table.

The secondary benefit is maintenance efficiency. Index rebuilds, statistics updates, and data archival operations can target individual partitions rather than the entire table. Rebuilding the index on last month’s partition is a maintenance window operation on a small dataset. Rebuilding the index on a 10-billion-row table is a multi-hour blocking operation.

-- Check whether a table is already partitioned and how many partitions it has
SELECT
    OBJECT_SCHEMA_NAME(i.object_id)     AS SchemaName,
    OBJECT_NAME(i.object_id)            AS TableName,
    i.name                              AS IndexName,
    COUNT(p.partition_number)           AS PartitionCount,
    MIN(p.rows)                         AS MinPartitionRows,
    MAX(p.rows)                         AS MaxPartitionRows,
    SUM(p.rows)                         AS TotalRows
FROM sys.indexes        i
JOIN sys.partitions     p ON p.object_id = i.object_id
                         AND p.index_id  = i.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND   i.index_id IN (0, 1)  -- heap or clustered index only
GROUP BY i.object_id, i.name
HAVING COUNT(p.partition_number) > 1   -- already partitioned
ORDER BY SUM(p.rows) DESC;

3 The Key Difference: Storage vs Manageability Beginner

This is the most important conceptual distinction and the one most often missed when DBAs debate compression versus partitioning as if they are alternatives.

Query performance from partitioning comes specifically from partition elimination, and only when the query filter includes the partition key column. A query on a partitioned table that does not filter on the partition key touches every partition and may actually perform worse than the same query on an unpartitioned table due to partition metadata overhead.

4 When Compression Is the Right Choice Intermediate

Compression delivers real benefit when these conditions are true:

• The table is read-heavy. Queries spend more time scanning data than inserting or updating it. Compression reduces page reads directly. The more pages a query reads the more it benefits.
• The data is compressible. Tables dominated by LOB or row overflow data cannot be compressed meaningfully. Tables with repetitive values in fixed-length columns compress exceptionally well.
• CPU headroom exists. Compression adds CPU cost on every read and write. If the server is already CPU-saturated, compression makes performance worse even if I/O improves.
• The table is large enough to matter. Compressing a table below 8 pages gains nothing. The overhead of compression infrastructure on tiny tables is not worth it.

PAGE Compression Is Right When

• The table is predominantly read with low update rates (under 40% of total operations)
• The data has repetitive values: status codes, category IDs, dates, standard amounts
• page_compression_success_count from sys.dm_db_index_operational_stats confirms pages are actually compressing beyond ROW compression
• The table is a data warehouse fact table or historical archive

ROW Compression Is Right When

• The table has moderate reads and moderate writes (PAGE CPU cost is too high)
• The table has many fixed-length columns where actual values are shorter than the declared type
• You want compression benefit with less CPU impact than PAGE
• PAGE compression testing shows minimal additional benefit over ROW for this specific table’s data patterns

COLUMNSTORE Is Right When

• The table is a large fact table with analytical query patterns (aggregations, GROUP BY, range scans)
• Read percentage is 80% or higher with very low write rates
• The table has at least 1 million rows (columnstore overhead is not justified below this)
• Enterprise or Developer Edition is available

5 When Compression Is the Wrong Choice Intermediate

Compression makes performance worse or adds no value in these situations. Each one is a real scenario that shows up regularly in production environments.

Write-Heavy OLTP Tables

Every INSERT, UPDATE, and DELETE on a compressed table requires the engine to compress or recompress the affected page. On a high-volume order entry table receiving thousands of inserts per minute, PAGE compression can add measurable CPU overhead that slows down the entire workload. The I/O savings from fewer pages are real but the CPU tax is higher. Run sp_estimate_data_compression_savings and measure CPU impact in a test environment before applying to any high-write table.

Tables with High LOB Content

A table where 60% of its storage is LOB or row overflow pages will see at most 40% of its pages benefit from compression. The storage saving is proportional to compressible content only. The SQLYARD advisor reports CompressiblePct for this reason. A table showing 35% compressible content is not a compression candidate regardless of its read/write ratio.

CPU-Saturated Servers

If the server is already running at high CPU utilization during peak hours, adding compression increases CPU pressure. The I/O benefit does not compensate when the CPU bottleneck becomes the limiting factor. Check wait statistics for SOS_SCHEDULER_YIELD and CPU queue depth before applying compression to a busy server. See the SQLYARD article on MAXDOP tuning for the wait statistics queries that surface CPU saturation.

Very Small Tables

Tables below 8 pages (64KB) have no meaningful compression benefit. The overhead of the compression infrastructure exceeds any storage saving. The default minimum page threshold in the SQLYARD advisor filters these out automatically.

Already Compressed Tables

Re-compressing a table that is already at PAGE compression to PAGE compression again is a wasted rebuild operation. The advisor detects current compression state from sys.partitions.data_compression_desc and reports it rather than recommending a redundant change.

6 When Partitioning Is the Right Choice Intermediate

Partitioning solves real problems for large tables but it is not a general-purpose performance tool. It is a manageability and targeted-access tool. It is right when these conditions are true:

• The table is large and growing. Partitioning overhead is not worth it below a few million rows. The standard guidance is to consider partitioning when a table exceeds 5 million rows and to strongly consider it above 10 million rows, particularly when the table is growing regularly.
• A natural partition key exists. Almost always a date column. The partition key should be a column that queries commonly filter on and that divides the data into roughly equal-sized ranges over time.
• Queries filter on the partition key. Partition elimination only fires when the WHERE clause includes the partition key column. If your application never filters on the date column there is no query performance benefit from date-based partitioning.
• Maintenance windows are a problem. Index rebuilds on large tables take hours and block reads during ONLINE rebuilds. Partition-level maintenance is faster and targets only the data that needs it.
• Data archival is regular. Switching old partitions out of the active table and into an archive table is a metadata-only operation that completes in milliseconds regardless of partition size. This is partitioning’s single most compelling operational benefit.

-- Check whether queries against a large table actually filter on date columns
-- If queries never filter on the partition key candidate, partition elimination
-- will never fire and partitioning provides no query performance benefit

SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count     AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count      AS avg_elapsed_us,
    LEFT(qt.query_sql_text, 300)                    AS query_text
FROM sys.query_store_query           q
JOIN sys.query_store_query_text      qt ON qt.query_text_id  = q.query_text_id
JOIN sys.query_store_plan            p  ON p.query_id        = q.query_id
JOIN sys.query_store_runtime_stats   qs ON qs.plan_id        = p.plan_id
WHERE qt.query_sql_text LIKE '%YourLargeTableName%'
ORDER BY avg_logical_reads DESC;
-- Review top queries against your large table
-- If none filter on the date column, partitioning will not improve query performance
-- It may still be worth it purely for maintenance efficiency

7 When Partitioning Is the Wrong Choice Intermediate

Partitioning is one of the most commonly misapplied features in SQL Server. These are the situations where it hurts more than it helps.

Small Tables

A table with 500,000 rows does not need partitioning. The partition metadata overhead, the complexity of the partition function and scheme, and the constraints it introduces on index design are not justified. Monitor growth and revisit when the table reaches several million rows.

OLTP Point Lookup Tables

A Customers table or a Users table is accessed by primary key. The queries do not filter on a date range. Partition elimination never fires. There is no maintenance benefit because the table is too small and its growth is too slow. Partitioning these tables adds complexity and potentially hurts performance by adding partition routing overhead to every point lookup.

Tables Without a Suitable Partition Key

The SQLYARD advisor detects date column presence specifically because date columns are the natural partition key for range-based partitioning. A table without any date or datetime column can technically be partitioned on an integer or other column but the design is more complex and the benefits are harder to realize. If no obvious partition key exists, do not force partitioning.

Heaps

Partitioning a heap table without a clustered index limits your options significantly. Partition switching requires that the source and destination tables have matching structures including indexes. A heap cannot switch partitions to a clustered table. The SQLYARD advisor flags heaps and recommends creating a clustered index before partitioning.

Tables Where Queries Do Not Filter on the Partition Key

This is the most common reason partitioning fails to deliver expected performance gains. If the application sends queries like SELECT * FROM dbo.Orders WHERE CustomerID = 12345 and the table is partitioned on OrderDate, SQL Server scans every partition. The query plan shows no partition elimination. The table is now more complex to maintain with no performance benefit and potentially slower queries due to partition overhead.

8 When to Use Both: The Combined Strategy Intermediate

For large tables that qualify for both, the recommended approach is partitioning first, then compression per partition. This order matters because compression can be applied differently to different partitions and historical partitions compress much better than active ones.

-- The combined strategy in practice:
-- Step 1: Partition the large table by date (monthly example)

CREATE PARTITION FUNCTION pf_OrdersByMonth (DATE)
AS RANGE RIGHT FOR VALUES (
    '2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01',
    '2024-05-01', '2024-06-01', '2024-07-01', '2024-08-01',
    '2024-09-01', '2024-10-01', '2024-11-01', '2024-12-01',
    '2025-01-01', '2025-02-01', '2025-03-01', '2025-04-01',
    '2025-05-01', '2025-06-01', '2025-07-01', '2025-08-01',
    '2025-09-01', '2025-10-01', '2025-11-01', '2025-12-01',
    '2026-01-01', '2026-02-01', '2026-03-01', '2026-04-01',
    '2026-05-01', '2026-06-01'
);

CREATE PARTITION SCHEME ps_OrdersByMonth
AS PARTITION pf_OrdersByMonth ALL TO ([PRIMARY]);

-- Step 2: After partitioning, apply PAGE compression to historical partitions
-- Historical data is read-heavy, rarely updated, compresses well
ALTER TABLE dbo.Orders REBUILD PARTITION = 1   -- Jan 2024
    WITH (DATA_COMPRESSION = PAGE);

ALTER TABLE dbo.Orders REBUILD PARTITION = 2   -- Feb 2024
    WITH (DATA_COMPRESSION = PAGE);

-- Step 3: Apply ROW compression to recent-but-not-current partitions
-- Recent data still has moderate write activity, ROW has lower CPU cost
ALTER TABLE dbo.Orders REBUILD PARTITION = 28  -- Mar 2026
    WITH (DATA_COMPRESSION = ROW);

-- Step 4: Leave the current active partition uncompressed or ROW only
-- Active partition has high insert/update rate, PAGE CPU cost not justified
ALTER TABLE dbo.Orders REBUILD PARTITION = 30  -- May 2026
    WITH (DATA_COMPRESSION = ROW);  -- or NONE if writes are very heavy

-- Check compression state per partition after applying
SELECT
    p.partition_number,
    p.rows,
    p.data_compression_desc                         AS CompressionType,
    pf.name                                         AS PartitionFunction,
    prv.value                                       AS BoundaryValue
FROM sys.partitions                     p
JOIN sys.indexes                        i   ON i.object_id     = p.object_id
                                           AND i.index_id      = p.index_id
JOIN sys.partition_schemes              ps  ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions            pf  ON pf.function_id  = ps.function_id
LEFT JOIN sys.partition_range_values    prv ON prv.function_id = pf.function_id
                                           AND prv.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID('dbo.Orders')
AND   i.index_id  = 1
ORDER BY p.partition_number;

9 The SQLYARD Compression and Partitioning Advisor Beginner

The SQLYARD Compression and Partitioning Advisor is a stored procedure that analyzes every user table and index in the current database and produces evidence-based recommendations for compression type, partitioning candidacy, and the priority order for addressing both. It uses only Microsoft’s documented DMVs and signals with no dependency on any third-party tools or scripts.

What It Uses

• sys.dm_db_partition_stats for allocation unit composition (in-row, overflow, LOB page counts)
• sys.dm_db_index_operational_stats for read and write workload ratios including the page_compression_success_count column which is Microsoft’s native signal for PAGE compression effectiveness
• sys.partitions for current compression state and partition count
• sys.columns for date column detection and suggested partition key

Parameters

-- Default run: heap and clustered index only, 8-page minimum
EXEC dbo.usp_SQLYARD_CompressionPartitionAdvisor;

-- Include non-clustered indexes in the analysis
EXEC dbo.usp_SQLYARD_CompressionPartitionAdvisor @IncludeNCIndexes = 1;

-- Lower threshold to include smaller objects
EXEC dbo.usp_SQLYARD_CompressionPartitionAdvisor @MinPages = 4;

-- Full analysis of everything
EXEC dbo.usp_SQLYARD_CompressionPartitionAdvisor
    @MinPages = 1,
    @IncludeNCIndexes = 1;

10 Reading the Output: What Each Column Means Intermediate

Priority Column Decision Logic

11 Testing Before You Commit Advanced

Never apply compression to a production table without testing first. The SQLYARD advisor tells you which tables are candidates. The testing process tells you how much benefit you will actually get and whether the CPU cost is acceptable on your specific hardware and workload.

Step 1: Estimate Savings with sp_estimate_data_compression_savings

-- Run the estimate command from the advisor's EstimateCommand column
-- This copies 5% of the table to TempDB and measures compression ratio
-- WARNING: Uses 5% of table size in TempDB -- verify free space first

-- Check TempDB free space before running on large tables
SELECT
    volume_mount_point,
    total_bytes / 1024 / 1024               AS TotalMB,
    available_bytes / 1024 / 1024           AS AvailableMB,
    CAST(available_bytes * 100.0
        / total_bytes AS DECIMAL(5,1))      AS FreePct
FROM sys.dm_os_volume_stats(2, 1);  -- 2 = TempDB database_id

-- Run PAGE compression estimate
EXEC sys.sp_estimate_data_compression_savings
    @schema_name      = 'dbo',
    @object_name      = 'YourTableName',
    @index_id         = 1,              -- 1 = clustered index
    @partition_number = NULL,           -- NULL = all partitions
    @data_compression = 'PAGE';

-- Also run ROW estimate to compare
EXEC sys.sp_estimate_data_compression_savings
    @schema_name      = 'dbo',
    @object_name      = 'YourTableName',
    @index_id         = 1,
    @partition_number = NULL,
    @data_compression = 'ROW';

-- Key output columns to evaluate:
-- size_with_current_compression_setting_KB  = current size
-- size_with_requested_compression_setting_KB = projected size after compression
-- sample_size_with_current_compression_setting_KB   = sample used
-- sample_size_with_requested_compression_setting_KB = sample compressed
-- The ratio tells you the expected compression benefit

Step 2: Test on a Non-Production Copy

-- Apply compression to a copy of the table in a development or test environment
-- Run your actual application workload against it for a representative period
-- Measure before and after:

-- Before metrics snapshot
SELECT
    GETDATE()                               AS SnapshotTime,
    'Before Compression'                    AS Label,
    wait_type,
    waiting_tasks_count,
    wait_time_ms
INTO dbo.CompressionTest_WaitsBefore
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX',     -- I/O waits (should decrease)
    'SOS_SCHEDULER_YIELD',                   -- CPU pressure (watch for increase)
    'CXPACKET', 'CXSYNC_PORT'              -- Parallelism (watch on rebuilds)
)
AND waiting_tasks_count > 0;

-- Apply compression to test table
ALTER TABLE dbo.YourTableName_TestCopy
REBUILD WITH (DATA_COMPRESSION = PAGE);

-- After metrics snapshot (after representative workload period)
SELECT
    GETDATE()                               AS SnapshotTime,
    'After Compression'                     AS Label,
    wait_type,
    waiting_tasks_count,
    wait_time_ms
INTO dbo.CompressionTest_WaitsAfter
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX',
    'SOS_SCHEDULER_YIELD',
    'CXPACKET', 'CXSYNC_PORT'
)
AND waiting_tasks_count > 0;

-- Compare: I/O waits should decrease, CPU waits should not spike

Step 3: Apply During a Maintenance Window

-- Apply compression to production with ONLINE = ON where possible
-- This reduces blocking impact during the rebuild

-- For SQL Server Enterprise Edition (ONLINE supported):
ALTER TABLE dbo.YourTableName
REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

-- For Standard Edition or when ONLINE is not available:
-- Schedule during lowest-traffic window
-- Monitor blocking during the operation
ALTER TABLE dbo.YourTableName
REBUILD WITH (DATA_COMPRESSION = PAGE);

-- Apply to a specific partition only (less impact than full table rebuild):
ALTER TABLE dbo.YourTableName
REBUILD PARTITION = 5
WITH (DATA_COMPRESSION = PAGE);

-- Verify compression was applied:
SELECT
    p.partition_number,
    p.rows,
    p.data_compression_desc
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID('dbo.YourTableName')
AND   p.index_id  IN (0, 1)
ORDER BY p.partition_number;

12 Applying Compression and Partitioning Safely Advanced

Both operations rebuild indexes internally. This has implications for maintenance windows, transaction log growth, and TempDB usage that must be planned for in production.

Compression: What Happens Internally

• Compression requires an index rebuild to rewrite all pages in the new compressed format
• The rebuild reads all data, compresses it, and writes it to new pages before releasing old pages
• Transaction log usage is significant: the operation is fully logged for recovery
• TempDB usage: sp_estimate_data_compression_savings uses 5% of table size in TempDB to run its estimate. The actual compression rebuild does not use TempDB directly but may impact TempDB during sort operations
• ONLINE rebuild reduces blocking but doubles temporary space usage during the rebuild period

Partitioning an Existing Table

-- Partitioning an existing table requires rebuilding the clustered index
-- on the partition scheme -- this is a full table rebuild operation

-- Step 1: Create partition function and scheme (no table impact yet)
CREATE PARTITION FUNCTION pf_YourTable (DATE)
AS RANGE RIGHT FOR VALUES ('2025-01-01', '2025-07-01', '2026-01-01');

CREATE PARTITION SCHEME ps_YourTable
AS PARTITION pf_YourTable ALL TO ([PRIMARY]);

-- Step 2: Drop and recreate the clustered index on the partition scheme
-- THIS IS THE OPERATION THAT REBUILDS THE TABLE -- plan accordingly
CREATE UNIQUE CLUSTERED INDEX CIX_YourTable_ID
ON dbo.YourTableName (YourPKColumn, YourDateColumn)
WITH (DROP_EXISTING = ON, ONLINE = ON)  -- ONLINE reduces blocking (Enterprise)
ON ps_YourTable (YourDateColumn);       -- aligned to partition scheme

-- Verify partitions after creation
SELECT
    p.partition_number,
    p.rows,
    prv.value   AS BoundaryValue
FROM sys.partitions                 p
JOIN sys.indexes                    i   ON i.object_id = p.object_id
                                      AND i.index_id  = p.index_id
JOIN sys.partition_schemes          ps  ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions        pf  ON pf.function_id   = ps.function_id
LEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id
                                       AND prv.boundary_id  = p.partition_number
WHERE p.object_id = OBJECT_ID('dbo.YourTableName')
AND   i.index_id  = 1
ORDER BY p.partition_number;

Download the SQLYARD Compression and Partitioning Advisor

The stored procedure covered in this article is available as a free download from the SQLYARD Tools page. It runs on SQL Server 2016 through SQL Server 2025, requires no third-party tools, and installs with a single script execution in any user database.

After downloading, open the script in SSMS, change the USE [YourDatabaseName] line at the top to your target database, and execute. The stored procedure is created in the dbo schema of that database and is ready to run immediately.

-- Quick start after installing:
EXEC dbo.usp_SQLYARD_CompressionPartitionAdvisor;

-- Include non-clustered indexes:
EXEC dbo.usp_SQLYARD_CompressionPartitionAdvisor @IncludeNCIndexes = 1;

References

• Microsoft Docs: Data Compression in SQL Server
• Microsoft Docs: Page Compression Implementation
• Microsoft Docs: Row Compression Implementation
• Microsoft Docs: sp_estimate_data_compression_savings
• Microsoft Docs: Partitioned Tables and Indexes
• Microsoft Docs: sys.dm_db_index_operational_stats
• Microsoft Docs: sys.dm_db_partition_stats
• Microsoft Docs: Columnstore Indexes Overview
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server MAXDOP Tuning Guide
• SQLYARD: SQL Server Index Tuning Guide