Data Compression in SQL Server 2008
We're back talking SQL Server 2008, hopefully with announcements of RTM around the corner. Today I want to cover one of the great new features in the upcoming release of SQL Server - data compression.
Data compression allows you to store your data more effectively and reduce the storage resources needed. The biggest winners of this feature will be instances that have large I/O bound workloads, such as data warehouses.
To give some perspective, a 4-byte integer in SQL Server 2005 would take up 1 byte + 4 bits per value with data compression enabled in SQL Server 2008. Apply this across millions of rows and you have some significant savings.
Compression can be applied at either row or page level for the following object types:
- Tables
- Nonclustered indexes
- Indexed views
Partitioned and non-partitioned tables may be compressed, with partitioned tables allowing further specification of row, page, or none at the individual partition level.
Indexes of compressed tables do not inherit the settings of their parent object. You must set each index individually.
Setting up compression can be done in a couple of ways. Like so many things SQL Server related, there is a wizard. For an existing item, right click the object in Object Explorer and choose "Storage" --> "Manage Compression..." as seen below.
Upon selecting "Manage" the wizard pops up to take you through the process.
The first step in the wizard displays the partitions associated to our object and allows us to set the compression type for each. Had this example table been partitioned we would have seen details for partition function boundary and a row count. We also have a handy "Calculate" button to show the compressed space necessary.
For this simple example I want to apply compression to all partitions (all one of them) so I select the smartly named "Use same compression type for all partitions" checkbox and choose from the drop-down list to the right.
The next step of the wizard presents us with options to either script the compression changes, run them immediately, or schedule them (presumably for an off-peak hour when you won't run your instance into a ditch). I really appreciate the choice given to me here as my needs may change drastically depending on the objects or environment where I happen to be working.
At this point we choose "Next" or "Finish" and receive the output of all our clicking:
USE [ExampleCode]
ALTER TABLE [dbo].[Invoice] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
Executing the query applies compression to the object, with no outward signs of compression to anyone viewing via Object Explorer or Intellisense.
To find the compression style of an object you can run the following query against the sys.partitions compatibility view:
SELECT *
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.Invoice', 'U');
The data_compression_desc column will list the compression style. To get statistics on specific partitions you can query the Dynamic Management Object function sys.dm_index_operational_stats:
SELECT page_compression_attempt_count, page_compression_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.Invoice', 'U'), NULL, NULL);
Like all things SQL Server, there are considerations before going out and running wild with compression:
- Compression is available only in Enterprise and Developer editions.
- Compression does not change the maximum row size of a table or index.
- A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. This check is done at initial compression and again as rows are inserted or modified.
- You can enable or disable ROW or PAGE compression online or offline. Enabling compression on a heap is single threaded for an online operation.
- The disk space requirements for enabling or disabling row or page compression are the same as for creating or rebuilding an index. For partitioned data, you can reduce the space that is required by enabling or disabling compression for one partition at a time.
Next time...encryption and announcement of another place you can come hear me speak, if you can bear it. ;)