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.