SQL Server Performance Tuning

Improve Your SQL Server Performance Enabling Data Compression

Updated October 29, 20253 min read

How to use SQL Server data compression. Ploom sits on a bulging suitcase, squeezing it shut.

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?

  1. Start with the objects from the query below (or you can start with the largest tables/indexes).
T-SQL
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)
  1. Determine if page or row-level compression is best for each object.
  2. Compress

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜