SQL SERVER – Script to Estimate Compression

Today we are going to see something very simple thing which can make a huge difference in performance. Recently I had implemented Data Compression at my client Comprehensive Database Performance Health Check. Let us discuss how we can estimate compression in SQL Server.

There are two different kinds of compressions in SQL Server that are widely popular 1) Page Compression 2) Row Compression.

Two Kind of Compressions

If you have data that is read more and updated less for that table, you can go for Page Compression. If your tables are updated very frequently it is recommended that you go with Row Compression. In general, I have seen page compression more effective than row compression but again, this can vary based on how your data is and how your system is set up.

Data Compression – Yes or No?

The biggest question I often receive is that should opt for compression. Well, the answer is pretty straightforward. If you need to save space, you can go ahead with data and index compression. Compressed table and index take more CPU cycles than non-compressed data to write to disk. This means, if you have a CPU pressure, the compression will further degrade the performance.

Estimate Compression

You can easily estimate compression for your data table or index using the following script.

Page Compression Estimation

EXEC sp_estimate_data_compression_savings
@schema_name = 'Production',
@object_name = 'WorkOrder',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'PAGE';

Row Compression Estimation

EXEC sp_estimate_data_compression_savings
@schema_name = 'Production',
@object_name = 'WorkOrder',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'ROW';

Once you check your estimation and if you do not have a CPU pressure issue, you can easily enable compression by running the following script.

Enable Compression

Enable Page Compression

ALTER TABLE Production.WorkOrder REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);
GO

Enable Row Compression

ALTER TABLE Production.WorkOrder REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);
GO

Disable Data Compression

ALTER TABLE Production.WorkOrder REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);
GO

Remember you should select any one kind of compression for your data table or index. You cannot apply both the kind of compressions together.

Let me know what you think of this blog post. If you want I can also create a video on this topic on my YouTube Channel – SQL in Sixty Seconds.

Reference: Pinal Dave (http://blog.SQLAuthority.com)