Proper Usage of MAXDOP Setting When Compressing Indexes in SQL Server 2008/2008 R2

I am a pretty big fan of SQL Server Data Compression, especially the enhanced version found in SQL Server 2008 R2 that includes Unicode compression. If you have tables or indexes that are good candidates for data compression (because they are large, relatively static, and show good compression savings), you should look extra closely for columns that are NVARCHAR and NCHAR data types. This is especially true if you mainly store Western language characters in those columns, since Unicode compression nearly makes Unicode data the same size as the equivalent non-Unicode data in that scenario.

If you already have compressed an index with ROW or PAGE compression in SQL Server 2008, and the index has any columns that are NVARCHAR or NCHAR data types, you should run the sp_estimate_data_compression_savings stored procedure against that index in SQL Server 2008 R2 to see how much additional space you can save with Unicode compression. You may be pleasantly surprised.

EXEC sp_estimate_data_compression_savings N'dbo', N'MachineSubscriptionChange', 1, NULL, N'Page';

If you have the max degree of parallelism setting for your SQL Server instance set to the default value of zero, then you need to be careful to set an appropriate value for MAXDOP when you create or rebuild an index in SQL Server. Otherwise, you may see all of your CPU cores go to 100% utilization while the index is being created or rebuilt. This will make it finish more quickly, but it could have a negative effect on your concurrency and performance during that operation. You should also use the ONLINE option when possible. This will make the index operation take somewhat longer, but you will not reduce concurrency like you do with an offline operation.

I typically set MAXDOP at 50% or less of my total CPU cores, so that I won’t use more that 50% of my total CPU capacity to create/rebuild the index. Below is an example of how to do this:

ALTER TABLE [dbo].[MachineSubscriptionChange] 
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE, MAXDOP = 8);
Below, you can see my Task Manager with eight of its CPU cores pretty much pegged while rebuilding and compressing a clustered index.

Remember that Data Compression is only available in Enterprise Edition…

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