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…