4

SQL SERVER - Script to Estimate Compression - SQL Authority with Pinal Dave

 2 years ago
source link: https://blog.sqlauthority.com/2021/12/15/sql-server-script-to-estimate-compression/?utm_campaign=sql-server-script-to-estimate-compression
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

SQL SERVER - Script to Estimate Compression dcompress-800x204

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)


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK