Improve your SQL Server performance enabling data compression

What is the data compression feature in SQL Server?

It is a feature introduced in SQL Server 2008 Enterprise Edition.

Later, Microsoft added to Standard Edition of SQL Server 2016 SP1 and higher.

Data compression can improve your SQL server performance, reducing I/O.

It support tables, clustered index, non-clustered index.

By the way, this check is a part of our SQL Server Health Check service.

Compression requires a bit more CPU, but it is not a problem when the majority of systems are IO-bound, not CPU-bound, so the trade-off is worth it.

SQL gains performance in multiple areas:

  • Data take less space in memory;
  • Backups and restores are smaller and, therefore, faster;
  • Reduces load on storage;
  • Helps always-on/mirroring/log shipping/replication “go” faster;

How to identify tables and indexes to use data compress?

  1. Start with the objects from query below (or you can start with the largest tables/indexes).
    1. SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name],
    2. OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id,
    3. CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
    4. COUNT(*) AS [BufferCount], p.[Rows] AS [Row Count],
    5. p.data_compression_desc AS [Compression Type]
    6. FROM sys.allocation_units AS a WITH (NOLOCK)
    7. INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
    8. ON a.allocation_unit_id = b.allocation_unit_id
    9. INNER JOIN sys.partitions AS p WITH (NOLOCK)
    10. ON a.container_id = p.hobt_id
    11. INNER JOIN sys.objects AS o WITH (NOLOCK)
    12. ON p.object_id = o.object_id
    13. WHERE b.database_id = CONVERT(int, DB_ID())
    14. AND p.[object_id] > 100
    15. AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
    16. AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
    17. AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
    18. GROUP BY o.Schema_ID, p.[object_id], p.index_id, p.data_compression_desc, p.[Rows]
    19. ORDER BY [BufferCount] DESC OPTION (RECOMPILE)
  2. Determine if page or row-level compression is best for each object.
  3. Compress.

More information:

Microsoft – Data Compression

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *