June 14, 2011 at 3:09 am
Hi,
So from what i gather, compression is good since it lowers IO as the the data is smaller so its quicker to read write and data is compressed in memory as well.
My question is. Will using something like Red Gate compressions still give benefits to say a mdf that is 20gb. Will it benefit a mdf that is 2gb! Ie. Does compression all ways give a performance boost. Or is there a size where the file is so small anyway that compressions wont really give any benefits.
S
June 14, 2011 at 3:26 am
There are 2 types of Compression in SQL Server
1-Row Compression
2-Page Compression(Row Compression + Prefix Compression + Dictionary Compression)
Performance will be decrease because when you retrieve the compressed data then it must be involve de-compress process and then you can see that data as a result but compression gives you space advantage
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 14, 2011 at 4:31 am
Hi thanks for the reply.
From what i had read. Therre was a slight CPU overhead for the use of compression but if you had spare CPU compacity (Our servers are running nowhere near maxed out!) Then it would give a bonus.
June 14, 2011 at 4:52 am
Compression gives a huge performance bonus at a slight cost to CPU. Yes, you're moving less to & from the disk, but even more importantly, you're storing more rows on a page, and this is especially true of indexes, which means you have to read fewer pages to retrieve more data. Since disks, even with SSD, are the biggest bottle neck to a system, anything you can do to reduce that bottleneck is well worth it. If you don't use compression for anything else, I'd recommend it for indexes, but honestly, it helps everything.
As to Red Gate (I do work there), yes, assuming you're talking about our Storage Compress product, you can get more compression and better performance out of the tool over SQL Server. It does come with an additional cost to CPU, but again, not one that overwhelms the benefits conferred by the improvement on disk reads. Further, we support all versions of SQL Server and all editions where as you get limits on SQL Server.
But, compression is good, Red Gate or not. I like Microsoft's compression. Ours is just better. 😎
"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
June 14, 2011 at 4:59 am
Hi Grant,
Does it matter about the original size of the database? Do you still get a bonus even if the database is already small? (All our mdfs are 1-40gb) Or is it only for these big beasty databases people have!?
Thanks
S
June 14, 2011 at 5:11 am
For really small sets of data, no, you won't see much of an improvement. But as you start stuffing more keys on a page for an index, you will see a difference, even on smallish databases (let's say, >20gb, but that's a ball park SWAG). Same thing goes for the data compression. If you only ever access 5 pages of data, getting that down to 4 pages just won't be noticeable. But take the same situation where you're reading 100 pages and take down to 80, and you'll see a difference.
"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
June 14, 2011 at 7:10 am
http://technet.microsoft.com/en-us/library/ms189628.aspx
the buffer manager per counter should help you decide if you want to go ahead with compression.
Page reads/sec
http://www.sql-server-performance.com/2005/monitor-io-counters/
For small db sizes the trade off time between CPU cost to compress and decompress might not make it worth the effort.
June 14, 2011 at 8:29 am
I know there are some articles out there (sorry I don't have a link), but the conclusion is that "it depends." I don't know of any hard and fast rules around compression however, I've tried to use it on several systems where disk was a serious bottle-neck. In some cases, mostly ETLs, the performance gain was pretty good. But, when I tried on other systems simple queries that take a second or two went up by a factor of 10 or more. So, about that only thing I can suggest is to test, test and test..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply