SQL Server 2008 Page Compression: Performance impact on inserts

re: SQL Server 2008 Page Compression: Performance impact on inserts

david wei — Tue, 13 May 2008 20:28:18 GMT

Can you add the following 2 lines before each insert, to make a cold start

dbcc dropcleanbuffers

dbcc freeproccache

re: SQL Server 2008 Page Compression: Performance impact on inserts

Linchi Shea — Wed, 14 May 2008 01:36:25 GMT

As mentioned in the blog, the script was run multiple time and that ensured that all the data pages from the customer table (the source table) were cached. DBCC DROPCLEANBUFFERS was not included on purpose. The script was intended to measure only the impact on the writes. Like any other engineering tests, I wanted to isolate the specific aspect of what I was testing, though I understand in real world scenarios you would have many factors all mixed in there, and therefore the difference may not be as pronounced.

re: SQL Server 2008 Page Compression: Performance impact on inserts

jchang — Thu, 15 May 2008 01:15:22 GMT

the right way to run this test is with source data in memory, what is the size of the table, normal and compressed by the way.

did you verify no check point occurred during the test?

anyways 3M rows in 80 sec is 37,500 rows/sec, which pretty good.

assuming this is favorite 2.3GHz processor, one proc was pegged during the test, excluding CPU from the lazy writer, and log writer, thats 61K cpu-cycles per row.

The basic cost of compression should not be that high relative to this per row insert cost. LiteSpeed could do something like 120MB per CPU-sec if I recall.

So the additional 137 CPU-sec (if one CPU was pegged) is rather high.

I would if the SQL compression engine is decompressing and recompressing the entire page for each row insert, rather than taking a block of rows and compressing it

re: SQL Server 2008 Page Compression: Performance impact on inserts

SQLDiva — Thu, 15 May 2008 09:00:34 GMT

Please call/email me regarding the 5/20 NJSQL meeting.

Melissa[AT]SQLDiva.com

SQL Server 2008 Page Compression: Performance impact on table scans

Linchi Shea — Sat, 17 May 2008 01:38:15 GMT

My previous post shows that data compression may not come for free, although hopefully by the RTM time