Adding Data Compression to an Existing Clustered Index in SQL Server 2008 R2

Working at an ISV like NewsGator Technologies, I get to work on products like Social Sites for SharePoint 2010, that use SQL Server 2008 or greater (along with SharePoint 2010). We don’t require SQL Server 2008 Enterprise Edition, but we try to seamlessly take advantage of it if it is present, with things like Data Compression, which is an Enterprise Edition only feature in SQL Server 2008 and above.

We have a table that is a perfect candidate for data compression (since it has a number of int and bigint data type columns which compress well, and the data never changes after the initial insert), so I thought I would add data compression support for the clustered index of the table. This will reduce the space used by this table by about 60%, and reduce the write I/O required by a similar amount. Remember, you cannot create or rebuild (or compress) a clustered index in SQL Server 2008 R2 in ONLINE mode if you have any BLOB data types in the table.

Here is a code sample showing how to check for Enterprise Edition, then check to see if the clustered index is already compressed, and then finally compress the clustered index in ONLINE mode, limiting the number of processor cores used for the index compression to 2. This will make the compression take slightly longer, but will reduce the concurrency impact on the system.

— Adding Data Compression in SQL Server 2008 and 2008 R2
— Glenn Berry
— October 2010
http://glennberrysqlperformance.spaces.live.com/
— Twitter: GlennAlanBerry

    — Check for Enterprise Edition, and use Page data compression on the clustered index if we have it
   IF SERVERPROPERTY(‘EngineEdition’) = 3
          BEGIN
              
— Check to see if we already have any compression on the index
              
IF (SELECT data_compression
                    FROM sys.partitions
                  
WHERE index_id < 2
                    AND OBJECT_NAME(object_id) = N’NM_FeedRetrieveHistory’) = 0
                   
                    BEGIN
                      
— Use Page Compression on the clustered index if we
                        — have SQL Server 2008 Enterprise Edition or greater
                      
ALTER TABLE[dbo].[NM_FeedRetrieveHistory] REBUILD PARTITION = ALL
                      
WITH(DATA_COMPRESSION = PAGE,ONLINE = ON,MAXDOP = 2);
                  
END
          END

This entry was posted in SQL Server 2008 R2. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s