SQL Database Compression

[nestava]

Anybody know if there are any guidelines on using Database compression in MSSQL to improve performance in JDE Enterprise One? It has been recommended but there is no guidance on the subject.

Thanks

 

[FSJCNC62]

You may find value reviewing the Advanced Tuning for JD Edwards Implementations book available on Amazon. There is a chapter with SQL Server 2008 examples on how to choose row/page compression and benefits along with other tuning suggestions. Customers that I have implemented compression and RCSI for SQL Server have been very happy with the improvements gained.
Others may be able to provide you some of their experiences as well.

Frank

 

[SKH]

Hi All,

I would be really interested to hear more about what SQL compression has been implemented at any live sites and the improvements? I've played with compression but agree there seems to be a lack of 'recommendations' regarding this in relation to JDE.

Thanks / Regards,

Sanjeev

 

[Chan Rana]

Interesting. This is first time i hear that compression improves performance. Wouldn't the compression decompression impact on performance?.

Chan

 

[SKH]

Chan,

On the contrary, there should be some performance gains. Our largest table is the G/L. In a Test environment, we managed to decrease the footprint by 60 odd percent. The link below is quite useful:

http://msdn.microsoft.com/en-us/library/dd894051(SQL.100).aspx

Thanks / Regards,

Sanjeev

 

[BOster]

It think it depends on where your bottleneck is. For us, and probably a lot of organizations that bottleneck seems to be the disk subsystem.

From my understanding, compression will increase CPU utilization on the DB server but decrease disk i/o. If you have lots of unused CPU cycles but your waiting on read/writes from/to the disk then this may improve performance.

Our CNC group has been working on implementing this for our systems. They are working on getting configured in our lower environments and at some point we will implement in our production environment.

 

[Larry_Jones]

One Collaborate 2009 session (Oracle presenters) that continues to stick in my head showed benchmarks on the impact of using Oracle (or SQL Server) database compression.

The big points:

- Average disk space savings of 60%.
- Average DB Cache savings also around 60% (memory savings)
- Inserts CPU overhead ~= 1%
- Updates CPU overhead ~= 2%
- Reads . . . 2.7 times faster . . . yes I said 2.7 times faster.

The catch is that you must have Enterprise edition of Oracle or SQL Server to use compression, and in Oracle's case compression is an extra cost option on top of that. On the other hand, 60% space/memory savings and 2.7 times faster read I-O can put off some hardware upgrades for several years . . .

 

[DBohner-(db)]

Is it actual database compression, or are we talking about compression of t he network communications?

 

[Phil_D]

we have implemented SQL Compression on several sites now, and in every case the performance has improved (all sites were IO bound). We are starting to see some sites with a lot of SSDs, and these tend to be CPU bound, but SSDs aren't cheap, so the space saving (while impacting performance as it increases CPU workloads) is significant. On Xe and 8.0 systems we achieved around 70-80% reduction, on later releases with Unicode data and SQL 2008 or later we have seen reductions approaching 90% (best was 1.3TB to 150GB). There is no need to determine if row or page compression suits particular tables better, or even to exclude tables with a lot of updates from any compression (e.g. Next Numbers) - you can make everything Page Compressed and still come out ahead. Note that in an upgrade from an old release JDE will recreate a number of tables - uncompressed (F0911, and F42199 usually being some of the biggest)- as will the Unicode Conversion, so you may need a heap more space for an upgrade. Despite Unicode dramatically increasing space used for uncompressed data, a compressed Xe database before an upgrade and a compressed E910 database after should have a very similar size(just allow 10x the space to get through the upgrade itself!)

 

[SKH]

Thank you Phil-D, just the kind of stuff I wanted to hear

Regards,

Sanjeev