SQL SERVER – Script to Enable PAGE and ROW Level Compression – Disable Compression

At midnight I got a call from a customer who earlier availed my service Comprehensive Database Performance Health Check. The issue which they were facing were sudden high CPU for their SQL Server. They sent me an SOS message for help and I responded. The entire issue was about Page and Row level compression. Let us read more about it today.

About 2 months ago, the customer had hired me for a SQL Server Performance Tuning Health Check, during that time we had put lots of performance tuning tweaks. The entire system ran fine for over 2 months and suddenly during one of the night, they once again faced the issue with SQL Server performance, when they reached out to me.

What Changed?

The very first question I asked them was what has changed since everything was working fine and now suddenly things are not working fine. After a while, they presented a list of the activities which they had done on their server since morning. While reviewing the list, one of the activity got my attention and that was they had enabled Page Level on one of the tables which was under heavy use and was the biggest table.

I immediately turned off Page Level Compression and their CPU came down to the earlier levels. After careful observation, we figured out that they did not have enough CPU to enable Page Level Compression. They reached out to their hosting company and added more CPU to their machine. Later on, we turned on the page level for them again and everything went fine.

Compression Scripts

Let us see scripts to Enable PAGE and ROW Level operation as well as Disable operation.

Page Level Compression

ALTER TABLE [NameofYourTable] REBUILD PARTITION = ALL
    WITH (DATA_COMPRESSION = PAGE);

Row Level Compression

ALTER TABLE [NameofYourTable] REBUILD PARTITION = ALL
    WITH (DATA_COMPRESSION = ROW);

Disable Compression

ALTER TABLE [NameofYourTable] REBUILD PARTITION = ALL
    WITH (DATA_COMPRESSION = NONE);

Let me know your feedback and opinion in the comments section. I am always eager to read your experience.

Reference: Pinal Dave (https://blog.sqlauthority.com)