SQL server 2008 Data Compression

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?

From serverfault $pageUsers[$entry.posts[0].author].name

• 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.

  Josip Medved : I tried it a little and it needs aprox. 20% of processor time for my particular data set. It would be best to test it since it is highly data dependant.

  Josip Medved : AWE steals a little of bandwidth, but it is still a lot faster that disk access. In practice, it AWE works as fast if your data set is not huge.
  From Josip Medved

• 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.

  As pages in memory also have compressed data,lot more data can be in memory,reducing I/O to a great extent.

  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..?

  From Jesper Mortensen

• 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.

  From Hakan Winther