On my OLTP database server main performance bottleneck is I/O.If i compress my database,as per my calculations i will have 60% compression.As pages in memory also have compressed data,lot more data can be in memory,reducing I/O to a great extent.In this scenario,will i get significant performance benefit derived from much less I/O?
It is a question of limited amounts of one resource (not enough disk I/O) prohibiting consumption of another resource (CPU time). The best way to go would be to add more disk I/O, so that all of your servers performance potential could be utilized.
If that is not feasible or needed for you, then I think you should enable compression. You will essentially be giving up a 'gratis' resource (CPU time, of which you have too much), to get more of a limiting resource (disk I/O). Above you write "Processor rarely spikes above 80%"; I take that to mean you have 20% - 40% free CPU most of the time. Enabling compression should work well for this server.
Mnn, that is correct, but maybe the performance impact will not be as large as you might hope. It will depend on the ratio between your cached data set and your 'working data set', before and after enabling compression.
Note that there are 2 levels of compression available to you. Maybe just enabling row compression would be a golden compromise for you..?
If your data is easily compressed (e.g. similar texts in text fields, etc.), and provided that you have lot of memory (in order not to hit swapping file), I can definitely say that things get faster.
Hard drive is really slow component so anything to avoid it does help.
P.S. This features uses quite a lot of processor so take that into consideration if your processor usage is already high.
You also have the option to compress the clustered indexs and not every index. This will give you a less cpu intence index seek, but you should gain IO performance when you actually get the datapages from the clustered index.
As a first step in our migration to SQL 2008, we activated page compression on the clustered indexes to see how much more CPU we are using. We gained aproximately 20% speed and still have a lot of CPU resources left (we are using a 4 x quad processor server), so in the next release we will compress all the other indexes also.
It is hard to predict how much you will benefit from any performance tuning and the solution is always TEST, TEST and more TEST.