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.

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).
    SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name],
    OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id,
    CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer SIZE(MB)],
    COUNT(*) AS [BufferCount], p.[ROWS] AS [ROW COUNT],
    p.data_compression_desc AS [Compression TYPE]
    FROM sys.allocation_units AS a WITH (NOLOCK)
    INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
    ON a.allocation_unit_id = b.allocation_unit_id
    INNER JOIN sys.partitions AS p WITH (NOLOCK)
    ON a.container_id = p.hobt_id
    INNER JOIN sys.objects AS o WITH (NOLOCK)
    ON p.object_id = o.object_id
    WHERE b.database_id = CONVERT(INT, DB_ID())
    AND p.[object_id] > 100
    AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
    AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
    AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
    GROUP BY o.Schema_ID, p.[object_id], p.index_id, p.data_compression_desc, p.[ROWS]
    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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.