Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


How Effective Is Data Compression?
Published: Dec 02, 2020
In this day and age with such a prolific speed in processor it’s relatively rare (although not unheard of by any stretch) that I hear people saying that they don’t have the CPU resources to run compression on their databases… but what I do regularly hear is people asking what space saving benefits it will give them.

Well, luckily, there is a stored procedure that Microsoft have built into SQL Server which you can use to answer exactly that question…

With that in mind, let’s try it out using one of the larger tables in my AdventureWorks database:

exec sp_estimate_data_compression_savings
              
@schema_name = 'sales',
              
@object_name = 'salesOrderDetailEnlarged',
              
@index_id = 1,
              
@partition_number = null,
              
@data_compression = 'row'
go



exec sp_estimate_data_compression_savings
              
@schema_name = 'sales',
              
@object_name = 'salesOrderDetailEnlarged',
              
@index_id = 1,
              
@partition_number = null,
              
@data_compression = 'page'
go



As per the above, you can clearly see the projected savings you would receive with each type of compression… obviously with row being a lower compression setting than page.

Note – I have to admit that I’ve slightly cut off the results from that query… there are two columns to the right which basically tell you that SQL Server took a sample, ran compression against that sample, and then projected up to the full size of the table… therefore you have to be careful because you need to know your data in order to know whether or not the sample is likely to be accurate for your whole table. However, as a whole, the prediction is pretty good and you can see that the savings are, in my case, very good indeed… I could halve the size of the table in question… not bad if you happen to be in a situation in which you have CPU to spare but disk and RAM are limited.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.