Thursday, January 10, 2013

SQL Server Row and Page Compression

One feature I haven't taken advantage of is SQL Server's Row and Page Compression.  The reason I've decided to look into it today is because - all things considered - in most of my instances, IO is one area I could improve performance.  Coupled with the fact that the CPU utilization hardly nears 30% and we've got the perfect situation to use compression.  Enabling compression on tables (clustered indexes) and non-clustered indexes will add a bit of CPU overhead, but when enabled on the appropriate objects that overhead will likely be offset by reducing IO.

One tool I had never head of is called the SQL Server Compression Estimator.  Prior to today I was using some scripts that included usage of sp_estimate_data_compression_savings.  This is a handy little tool that allows you to point to an instance/database and upon setting some bare bones configuration you are off and running.  The tool will give you the current size of each object, compressed size, percent savings and if you choose it will also give you the update ratio.  You will want to do additional analysis of objects with a high update ratio - compressing those objects can actually have an adverse effect on performance (a very active logging table that is never read from, for example).

One gotcha that I'm real glad I read was the fact that compression is only supported in Developer or Enterprise Editions.  That not only applies to the instances where you enable compression, but keep in mind the editions of SQL Server you may restore to in the event of a disaster, or what version you may be running locally on your laptops (in case you pull copies of databases and restore for development purposes).  I have verified this by creating a database on my development box and tried to restore on one of my servers running Standard Edition - the following error will occur: