Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

image

Remember that Data Compression is only available in Enterprise Edition…

Comments

Posted by Steve Jones on 18 August 2010

Great tip, one that some people might forget. Do you always keep MAXDOP at 8, or only when rebuilding indexes.

Posted by Patrick LeBlanc on 18 August 2010

Good information.

Posted by Glenn Berry on 18 August 2010

Steve: Well, I am talking about setting MAXDOP at the statement level when rebuilding an index. That overrides the instance level setting for that statement only.

I typically leave MAXDOP at the default of zero at the instance level (sp_configure).

Posted by mitch.fh on 18 May 2011

it is interessting for me, that you use parallelism when writing.

Would that no fragment the data as you have 8 threats that start writing in 8 different pages and for that writing coherend data in different pages?

Leave a Comment

Please register or log in to leave a comment.