1

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?

1
  • what are your processors, what speed is your memory and are you using 32 or 64 bit windows/sql?
    – Chopper3
    Aug 13, 2009 at 14:02

3 Answers 3

1

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

0

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.

4
  • Thank you for the answer. I have 16 GB of RAM and 15 GB is allocated to SQL server. How much processor load will be added?Processor rarely spikes above 80% in current scenario.
    – Kuldeep Patil
    Jul 31, 2009 at 7:02
  • Also i have 32 bit application.So memory is used through AWE settings.Does it have any adverse impact?
    – Kuldeep Patil
    Jul 31, 2009 at 7:04
  • 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. Jul 31, 2009 at 10:01
  • 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. Jul 31, 2009 at 10:03
0

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.

You must log in to answer this question.