Search results matching tags 'SQL Server 2008', 'Data Compression', and 'Performance'

SQL Server 2008 Page Compression: Performance impact on table scans

Linchi Shea — Sat, 17 May 2008 01:28:00 GMT

My previous post shows that data compression may not come for free, although hopefully by the RTM time the adverse performance impact on inserts will have been reduced significantly. In this post, I want to show you that data compression can enhance read performance. Intuitively, data compression may reduce the number of pages SQL Server must read into memory from disk, and the savings in terms of the number of pages can be significant, and therefore there may be a positive impact on reads.

To continue our tradition of letting data points do the talking, I ran the following simple script with the customer table when (1) it was page compressed and when (2) it was not page compressed. This was the same customer table I described in an earlier post on page compression and multiple processors.

dbcc dropcleanbuffers;
declare @dt datetime;
select @dt = getdate();

select avg(c_balance) from customer;

select datediff(second, @dt, getdate());
go

Note that there was no index on the c_balance column, and I verified that the actual execution plan was a table scan (well a clustered index scan). DBCC DROPCLEANBUFFER was included to ensure pages were read from disk, not already cached. The following chart summarizes the results.

Wow! The elapsed time difference between the compressed data and uncompressed data was huge. It took 52 seconds to scan the table when it's not compressed, but it took only 3 seconds when the table was compressed. Note that when uncompressed, the table had 2264 pages, and when compressed it had 534 pages.

Two more points to note: First, I did not try to explain why the improvement in table scan performance seemed to be disproportional to the reduction in the table size as the result of page compression. But I did run these tests many times to make sure I was not going to give you incorrect data. Second, I would caution you not to get too hung up on these specific numbers. As they often say, your mileage may vary. Instead, focus on the big picture impression that page compression may give your reporting queries a performance boost. In a real app, life is not so simple, and you would have to strike a balance between better reporting queries and potentially longer batch/data feed processing time when you consider page compression.

SQL Server 2008 Page Compression: Performance impact on inserts

Linchi Shea — Tue, 13 May 2008 02:49:00 GMT

Before I begin, let me be very clear that the results I report in this post were obtained with SQL Server 2008 CTP6. Microsoft is still working on improving the performance of data compression. And in particular, the performance of inserting into a compressed table is expected to be significantly enhanced in SQL Server 2008 RTM.

In my previous post, I have shown that data compression can result in significant savings in storage space. In this post, I want to highlight the fact that not everything comes for free. Let's take a look at inserting into a B-tree table, i.e. a table with a clustered index. What is the performance impact of page compression on inserts?

Note that you must use TABLOCK hint for pages to receive page-level compression when the pages of a heap table are being populated with the INSERT statement. So, heap tables are much less interesting, and I'll hence focus only on B-tree tables.

In my tests, I ran the following script:

truncate table customer2;
-- enable page compression
alter table customer2 rebuild with (data_compression=page);
go
declare @dt datetime;
select @dt = getdate();
insert customer2 select * from customer;
select 'insert into customer2 with compression', datediff(second, @dt, getdate());
go
truncate table customer2;
-- disable compression
alter table customer2 rebuild with (data_compression=none);
go
declare @dt datetime;
select @dt = getdate();
insert customer2 select * from customer;
select 'insert into customer2 without compression', datediff(second, @dt, getdate());
go

The script measures the difference in the elapsed time of inserting 3,000,000 rows into (1) an empty table when page compression is enabled, and (2) an empty table when no data compression is enabled. The script was run multiple times to ensure that the elapsed times recorded were consistent. The following chart shows the results:

Clearly, the impact of page compression on inserts was rather significant in the tests. Without data compression, inserting 3,000,000 rows took ~80 seconds, whereas it took 217 seconds when page compression was enabled on the target B-tree table. No free lunch!

Again, these numbers were obtained with SQL Server 2008 CTP6, a work in progress build. I'll re-run the test once SQL Server 2008 RTM is released.

Now that I have shown that there can be performance penalty in using data compression, next time I'll show that data compression can also help improve your performance.

SQL Server 2008 Page Compression: Using multiple processors

Linchi Shea — Mon, 05 May 2008 13:59:00 GMT

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';
go
DBCC DROPCLEANBUFFERS
GO
declare @dt datetime;
select @dt = getdate();
alter table customer rebuild with (data_compression=page, maxdop=2);
select 'maxdop = 2', datediff(second, @dt, getdate());
go
exec sp_spaceused 'customer';
go

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.