Cleveland DBA

SQL Server administration in Cleveland, OH

A little more about compression

Happy Monday folks!  Ready to talk some more about compression?

Unicode Compression

Another type of compression new in SQL Server 2008 is Unicode compression.  This is basically row compression for nchar and nvarchar datatypes.  Per BOL, SQL Server uses the Standard Compression Scheme for Unicode (SCSU) algorithm to achieve up to 50% space savings on some european languages, less so with Asian languages but still respectable.  If you really want to know more about what goes on behind the scenes in unicode compression, start with the wiki for the SCSU algorithm and go from there.

Backup Compression

The compression feature that I, personally, have made the most use of thus far would have to be backup compression.  Not only has this greatly reduced the size of my database backups, but it has also increased backup speed (less I/O).  The actual compression ratio is dependent on the data being backed up, whether that data is encrypted (this will not compress as much), and whether the database itself is already compressed.

Like the other types of compression, backup compression does use more CPU, but since we’re usually taking backups during quieter hours, I’ve not noticed any impact on performance.  If performance is a concern, you can always use resource governor to throttle how much CPU the backup process can use.

Backup compression can be used on an ad-hoc basis using the WITH COMPRESSION option of the BACKUP DATABASE command or it can be set globally using sp_configure (“EXEC sp_configure ‘backup compression default’, ‘1’;”).  If you set the compression default at the server level, this can always be overridden at the time of the backup.

Compression Considerations

Now before you run off and start compressing everything, there are some things you might want to keep in mind.

  • Performance – Nothing in life is free and this is no exception.  As I’ve mentioned before, the trade-off for disk savings is increased CPU usage.  If you’re already processor-bound, you might want to think twice before implementing data compression.
  • Version – Compression is currently only available in the Enterprise and Developer editions.  A compressed database cannot be restored to a Standard Edition instance.
  • Partitioning – You can compress (or not compress) each partition individually, and each partition can use a different type of compression.
  • Indexes – Clustered indexes inherit the compression property of the table, non-clustered indexes do not.  If you change the compression settings on a heap, all non-clustered indexes will be rebuilt.
  • Replication – By default, the initial snapshot will contain the same compression settings as the published object(s).  You can change the replication defaults to control whether compression settings are replicated.  If you try to replicate a compressed table to a Standard edition database, replication will fail.
  • Monitoring – You can monitor compression via the Page compression attempts/sec and Pages compressed/sec counters in the Access Methods object of Perfmon.

May 23, 2011 Posted by | Features | , , , | Leave a comment

Row compression

It’s said that there are 2 things you can count on in life: death and taxes. For DBAs, I’d like to add a third: database growth. Let’s face it, our need to store more and more data is only increasing and so is our need to retain this data for longer periods of time. And when you add those two trends together, you get a database that just keeps getting bigger. Kinda like the blob.

SQL Server 2008 introduced a few new compression features to help alleviate some of the growth problem; backup compression, and two types of data compression, row and page. Today I’m going to briefly discuss row compression.

Row compression is pretty simple, really. In a nutshell, SQL Server takes fixed-length data and stores it in a variable-length storage format. Blank characters aren’t stored, neither are NULL and 0 values. It doesn’t change the definition of the table or columns, so there’s no impact to your application. Let’s take a look at an example using the AdventureWorks database.

First we’ll check out the physical stats for the Purchasing.PurchaseOrderDetail table.

USE AdventureWorks;
GO

select index_id, record_count, page_count, avg_page_space_used_in_percent, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, compressed_page_count
from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('Purchasing.PurchaseOrderDetail'), NULL , NULL, 'DETAILED')
where index_level = 0;
GO

In particular, note the 3 columns dealing with record size, min_record_size_in_bytes, max_record_size_in_bytes, and avg_record_size_in_bytes. We can also look at the table properties.

Again, note the data space value.

Now let’s enable row compression on this table and re-run our query on sys.dm_db_index_physical_stats .

ALTER TABLE Purchasing.PurchaseOrderDetail REBUILD WITH (DATA_COMPRESSION = ROW)
GO

select index_id, record_count, page_count, avg_page_space_used_in_percent, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, compressed_page_count
from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('Purchasing.PurchaseOrderDetail'), NULL , NULL, 'DETAILED')
where index_level = 0;
GO

Note the change in the record size columns. And if we look at the table properties:

Not too shabby. Now, for a small table like this, the alter table took no time at all. But since this process is actually restructuring data pages, it can be quite expensive and time-consuming on a large table. So Microsoft provided a stored procedure that allows you to estimate your space savings before you commit.

EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL,NULL,'ROW'

In my next post, I’ll go over page compression.

P.S. – I really need to work on more creative and exciting post titles.  Maybe “Row Compression!!!”  Everything’s more exciting with exclamation points!!!

P.P.S – Portland was fantastic!!!  (<– see?  exciting.)

May 17, 2011 Posted by | Features | , | Leave a comment