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?
- 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)
- Determine if page or row-level compression is best for each object.
- Compress.
