SQL Server 2008 has introduced a long sought after feature -- Data Compression. This is a great feature, and I have no doubt it'll be widely used. The key compression method is called page compression, which uses the following three techniques to reduce the space taken up by duplicates on a page:
- Row compression. This technique changes the storage formats of many fixed-length datatypes (e.g. char, int, money, binary, datetime, and so on) so that they occupy only the required number of bytes plus a small overhead.
- Prefix compression. This technique finds duplicate prefixes on a page for each column , and replaces each duplicate with a small reference number.
- Dictionary compression. This technique finds duplicate values on a page, collects them into a dictionary stored after the page header but before the data rows, and replaces the duplicate values with their corresponding offsets in the dictionary.
You can read more about SQL Server 2008 data compression in SQL2008 CTP6 Books Online.
In this post, I'll focus on a very specific question: How does the number of processors impact rebuilding a table with page compression? Note that one way to enable page compression on a table is to rebuild it with the option data_compression set to page. The following is an example:
ALTER TABLE customer REBUILD WITH (DATA_COMPRESSION=PAGE);
Since compressing a table is a CPU intensive operation, one naturally would expect that using multiple processors should help reduce the elapsed time of the above command. How do you instruct SQL Server 2008 to take advantage of multiple processors? There exists another rebuild option called MAXDOP, which you can use to inform SQL Server of the max number of processors you want the rebuild to use. The following is an example of setting the MAXDOP option to 8:
ALTER TABLE customer REBUILD WITH (DATA_COMPRESSION=PAGE, MAXDOP=8);
To find out the impact of multiple processors (or the impact of setting MAXDOP to different values) on the elapsed time of the above command, I ran a series of tests with the command by setting MAXDOP to 0, 1, 2, 3, 4, 5, 6, 7, and 8 in turn. Before I report the test results, I should give you some information about the test setup:
- The TPC-C customer table was used to produce all the test results reported here. However, the original customer table in the TPC-C benchmark was not very compressible because its data didn't include many duplicates. For test purposes, I 'cooked' the table to make it more compressible. After I cooked it, the data size of the table was ~2,181,864KB without data compression, and was ~439,672KB with page compression.
- The test server had 8 processors (with four dual-core sockets), and 8GB physical memory.
- The data point for each MAXDOP setting was collected with the following T-SQL script. While only MAXDOP = 2 is shown below, in the test, this script was repeated for each of the MAXDOP values 0 through 8.
exec sp_spaceused 'customer';
declare @dt datetime;
select @dt = getdate();
alter table customer rebuild with (data_compression=page, maxdop=2);
select 'maxdop = 2', datediff(second, @dt, getdate());
exec sp_spaceused 'customer';
The following chart summarizes how the MAXDOP setting affects the elapsed time of rebuilding the customer table with page compression.
A number of observations should be noted:
- SQL Server 2008 page compression can effectively take advantage of multiple processors to speed up rebuilding a table. Using more processors generally leads to shorter elapsed time.
- The diminishing return on the number of processors is clear from the chart. In this particular test environment, after 5 or 6 processors, adding another processor to rebuilding the table with page compression only reduced the elapsed time only marginally. The largest improvement was obtained when the number of processors was increased from one to two.
- When the MAXDOP option was set to 0, its effect was the same as when it's set to 8, which happened to be the total number of processors on the server.
- For this test setup, changing the buffer pool size from 6GB to 2GB did not materially alter the test results.
- With MAXDOP=8, all eight processors were seen to be used 100%--as reported by the perfmon counter Processor\% Processor Time--during the table rebuild.