Inside Data Compression in SQL Server and Azure SQL: Performance Gains, Trade-offs, and Engine Inter

Microsoft SQL Server, Azure SQL Database, and Azure SQL Managed Instance support row and page compression for rowstore tables and indexes. This feature conserves storage space and can significantly improve the performance of I/O-intensive workloads by reducing the number of pages required to store data—thereby minimizing disk reads during query execution.
Compression, however, comes with trade-offs: it requires additional CPU resources for compressing and decompressing data as it moves between the database and the application. In this session, we will explore how Azure SQL implements compression internally using the column descriptor format, and examine its impact on overall system performance.
We will discuss potential side effects such as page splits or forwarding records, which can arise from compressed storage structures and influence query behavior. Additionally, we will highlight scenarios where compression cannot be applied—due to limitations in data types, table structures, or workload characteristics—and how to identify these cases in practice.
Finally, we will address the key question: does compression actually improve performance for your workload—and under what conditions might it not?
By the end of this session, you will be equipped to make informed decisions about when and how to apply data compression in SQL Server environments, balancing storage efficiency, CPU overhead, and query performance.

* Understand the internals of row and page compression and how they affect storage, I/O, and CPU usage
* Learn how to identify workloads and table structures that benefit from compression—and those that do not
* Apply practical guidance to balance compression benefits against side effects such as page splits and CPU overhead