July 30, 2009 at 5:34 am
Hi,
I've been reading up on Page and Row compression and like most it seems, have been impressed with compression benefits. However I then wanted to understand how SQL deals with retrieving compressed pages or rows and what overhead there must be. I've seen no information on this so far but did come across someone asking a similar question and also giving examples of poorer retrieval times after compression, however nobody seemed to be able to answer him.
So although compression on the face of it has many benefits, whats the cost of this when then having to retrieve those compressed pages or rows ??
Thanks
July 30, 2009 at 7:35 am
I have yet to put this into production, but in the dev & test servers where I've been using compression there are many positives regarding reads. The writes don't seem to be adversely affected that I've seen from the testing & use so far. Since there ain't no such thing as a free lunch, the payment for this has to come from somewhere and it's in the CPU. The CPU usage increases. I don't have an exact number, but it's not very big, maybe 1-2% (SWAG).
The reads really benefit both in terms of data and especially for indexes since there are fewer pages to move off the disk. That speed increase in disk & memory far outweighs the addition of a few cycles in the CPU to compress & uncompress the data. Overall it's been a very positive experience... so far.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2009 at 8:57 am
If you are speaking I/O, there shouldn't be a negative. There is less data to retrieve (because of compression) and so there is less physical I/O. As mentioned by Grant, the CPU is what takes the hit.
So if the queries are slower from the client, then likely you are CPU bound.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply