Everything AX: Dynamics AX running on a compressed SQLServer 2008 database

[Blog bot]

Источник: http://www.crankturner.com/2008/09/d...ompressed.html
==============

 

At AXUG Summit this month, I could not believe that Microsoft representatives were recommending to run Dynamics AX database in compressed mode on SQLServer 2008 (and I'm not talking about compressed backups). But hey, that's what the test server is for, right?

In theory...

How can a compressed database run faster than a non-compressed database? The reasoning given is that the compressed data pages stay in memory compressed. This is like getting about a 50% boost in memory for your database server.

Our environment...

In the company I work for, we are running 125Gb database with the backing store as an Equallogics SAN set in RAID-5 mode (long story, don't ask, it is what it is, sigh). Of course, that virtually guarantees that our Dynamics AX is I/O bound - every improvement we can make to our I/O sub-system will directly translate into improved database performance.

With compression turned on, the database server made about significantly less traffic to the SAN - that translates to an amazing huge performance gain.

Warning, before trying this, if you see your CPU regularly over 75% utilization, this may not be right for you - your system may be CPU constrained - and improvements to the disk I/O will not help. Added to that, the compression/decompression requires some (but less than I expected) CPU overhead.

How to...

We have migrated our data to SQLServer 2008, but it was difficult to understand how to run it "compressed". It turns out that enabling compression means to enable compression on a table by table, index by index basis (about 1000 objects in Dynamics 2009). Ouch. Time to Google.

I found the solution in SQLServer 2008 Bible, along with 2 great stored procs which can be downloaded here.

db_compression_estimate estimates the row and page compression gain for every table and index in the database.

db_compression (@minCompression) will check the current compression setting and compare it to potential row and page compression gains. If the compression should be adjusted it performs the alter command. The default @minCompression setting is 25%.

If the estimated gain is equal to or greater than @minCompression parameter then it enables row or page compression whichever is greater gain. If row and page have same gain then it enables row compression.

If estimated gain is less than @mincompression parameter then compression is set to none.

More info...

If you are interested in SQLServer 2008, the SQLServer 2008 Bible is a must read.

Full database compression is a seriously lengthy operation. On our database, it took over 12 hours of pinning (maxing out) the database and SAN. Plan accordingly.

Until next time...

    ...Crank

Technorati Tags: SQLServer 2008,database compression,Dynamics AX 2009



Источник: http://www.crankturner.com/2008/09/d...ompressed.html