What is the data compression feature in SQL Server?
It is a feature introduced in SQL Server 2008 Enterprise Edition.
Later, Microsoft added it to Standard Edition of SQL Server 2016 SP1 and higher.
Data compression can improve your SQL Server performance, reducing I/O.
It supports tables, clustered indexes, and non-clustered indexes.
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 compression?
- Start with the objects from the 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
