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.)