SQL SERVER – 2008 – Introduction to Row Compression

In my previous article SQL SERVER – 2008 – Introduction to New Feature of Backup Compression I wrote about Row Compression and I have received many request to write in detail about Row Compression. I like when I get request about any subject to write about from my readers.

Row Compression feature apply to zeros and null values and optimize their space in SQL Server. In fact, due to Row Compression feature SQL Server does not take any disk space for zero or null values. Any datatypes (decimal, datetime, money, int etc) if they are storing zero or null values in their rows, Row Compression feature does not store them in database at all.

Let us apply Row Compression to one of the table in database and see the performance with regards to storage compression. We can check beforehand about how much compression it will provide using stored procedure sp_estimate_data_compression_savings. Let us try to apply compression on AdventureWorks database table Production.WorkOrder.

sp_estimate_data_compression_savings 'Production','WorkOrder',NULL,NULL,'ROW'

Above SP will give you as many as resultset as many are index on table. Resultset of above SP will tell few of the statistic like current compression, future compression after requested compression, current rows and future reduced rows after requested compression. If you see reduction in the size of the table, you should go ahead and apply the row level compression.

To apply row level compression whole table needs to be REBUILD with Data_Compression = ROW clause enabled. Following command is used to rebuild the table.

ALTER TABLE Production.WorkOrder REBUILD
WITH (DATA_COMPRESSION = ROW)

After applying row compression size of the table is reduced by more than 1MB in size.This is one of the most valuable feature of SQL Server 2008 and I am going to use this feature on our production server as soon as we will upgrade our servers.

Additionally, there is concept of Page Compression as well and it is useful to do when there is lot of frequently occurring data. I will run few tests on live server and will post analysis when either of them should be used. I suggest trying with Row Compression first and take advantage of this new data storage improvement feature.

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

About these ads

16 thoughts on “SQL SERVER – 2008 – Introduction to Row Compression

  1. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  2. i want indetail about the Dirrential backup.

    but i don’t know how to use that.

    i am daily getting the full backup but it takes so much of time.

    Please Consider this sir,

    Like

  3. Sir
    It is really helpful for me. I have gone through all of sql server 2008 interview questions. The way you are explaining is really excellent. Please publish more questions.

    Like

  4. please send me querry to execute an dapply row compression. I am not able to apply above querry properly
    kindly send me proper syntex and method to compress it

    Like

  5. Hi Ashish,

    The comprassion can be enabled to new tables/nex as well as to existing table/index. The syntax to enable the comprassion is follwoing.

    CREATE TABLE TableName
    (
    column defination,….
    )
    WITH (DATA_COMPRESSION = ROW|PAGE|NONE)

    CREATE INDEX IndexName ON TableName (column,…) WITH (DATA_COMPRESSION = ROW|PAGE|NONE)

    ALTER TABLE TableName REBUILD WITH (DATA_COMPRESSION=ROW|PAGE|NONE)

    ALTER INDEX IndexName REBUILD
    WITH (DATA_COMPRESSION=ROW|PAGE|NONE)

    Please let us know what is the error you are getting.

    Kind Regards,
    Pinal Dave

    Like

  6. Pingback: SQL SERVER – Fastest Way to Restore the Database Journey to SQL Authority with Pinal Dave

  7. Dear Pinal

    After the row or page compression I/O performance will increase but what about the cpu performance. How to decide on page or row compression which should not affect the cpu performance much.
    Please provide any article on deciding the row and page compression selection which show I/O and cpu performance statistics also.

    Like

    • Hi

      –display all the compressed tables

      select a.name,b.data_compression_desc from sys.partitions b
      inner join sys.tables a on
      a.object_id = b.object_id where data_compression 0

      or

      select a.name,b.data_compression_desc from sys.partitions b
      inner join sys.tables a on
      a.object_id = b.object_id where data_compression_desc ‘NONE’

      please u can check.

      Ananthesh

      Like

  8. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31 Journey to SQLAuthority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

  10. “If you see reduction in the size of the table, you should go ahead and apply the row level compression.” I’m going to have to respectfully disagree with you there PInal.

    Like most concepts in SQL Server, this type of compression has both positive and negative consequences. Both row and page compression will increase CPU usage. This is a natural result of the compression and decompression processes involved with pushing and pulling the data out of storage when requested/inserted/etc. In certain scenarios, it can increase CPU quite substantially.

    On the other hand, the reduced file sizes lower your net IO. There’s your positive. Obviously, another positive is your storage requirements shrink.

    On a system that’s already CPU-bound, compression’s really not a good idea. Additionally, small tables aren’t usually good candidates for compression. The CPU cost clearly outweighs the storage and IO advantages.

    Thanks for the informative article, but I think we’d be doing a disservice by simply saying “if it reduces the size, compress it.”

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s