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 (https://blog.sqlauthority.com)
13 Comments. Leave new
Is there a performance difference when using row compression? Surely there are space savings, but you never get something for nothing. I imagine there is a small computational cost that is attached to these storage savings.
Absolutely. This feature is used for archieve tables. Not god for live tables.
Very interesting
Thanks Sir,
its very good.
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,
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.
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
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
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.
Please tell how can i get a list of compressed tables in the database ?
Thank you in advance
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
“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.”
How compression works …! On what factor does it depends..