Microsoft Dynamics AX (Archived)

Database compression

(0) Share

Report

Posted on by Hassan_rulz

3,801

Hi Experts,

We want to implement "Compression" functionality in our AX environment (AX R3 Cu11 with SQL server 2014 Sp2).

Our AX DB size is less than 150 GB.

Can you tell me if we enable compression in our environment then we can enhance performance?

and please also tell me if we can enable compression from ax side as well?

Regards,

Hassan

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I don't think that adding extra work for compression and decompression is going to improve performance.

I'm not sure what you mean by "from AX side". If you're still talking about database compression, it's a responsibility of SQL Server and AX knows nothing about it.

Was this reply helpful? Yes No
Suggested answer

Mahmoud Hakim 17,887 on at

Like (1)

Report
Copy link

Link copied!

If you ask about compression from AX system administration, I think this is good for performance, back and restore in huge database size

you can access this links

blogs.msdn.microsoft.com/.../ax-2012-database-how-to-enable-sql-compression-enterprise-edition

blogs.msdn.microsoft.com/.../sql-server-data-compression-for-dynamics-ax-1-introduction-and-best-practices

blogs.msdn.microsoft.com/.../sql-server-data-compression-for-dynamics-ax-2-planning-database-compression

blogs.msdn.microsoft.com/.../sql-server-data-compression-for-dynamics-ax-3-how-to-compress-dynamics-ax-databases-via-sql-or-ax

Was this reply helpful? Yes No
Martin Dráb 239,392 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

I seriously didn't know that the option for table compression is exposed in AX GUI. Still learning... :)

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report
Copy link

Link copied!

Some time ago I wrote about this, not sure if they have patched it up already to support later SQL versions:

www.daxrunbase.com/.../sql-data-compression-for-dynamics-ax-2012

The SQL compression for your DB size is not recommended. We have a 2 TB AX database where the larger tables (SalesLine, InventTrans, etc.) are of the size of your entire database, and we still do not require compression to have great performance.

I would not bother applying compression for anything smaller than a 20-50 GB single table. You would only have to consider the ratio of reads (scans - S) and writes (updates - U), it has to be minimum 75% and maximum 20-25% as a rule of thumb, otherwise it is not worth applying compression for that index.

technet.microsoft.com/.../dd894051(v=sql.100).aspx

At the time when I looked into this I also found scripts that could do the dirty work for you, and make recommendations that which tables are good compression candidates.

But again, a 150GB DB is nothing to worry about. Big performance boost will not really come from there, but from a good disk system (flash storage/SSD that is not in a SAN, but are local to your DB server).

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (1)

Report
Copy link

Link copied!

By Ax database standards your database is relatively small, so I agree with the above comments.

Generally it will give a performance boost for several reasons e.g, reduced iops but you need fast disks and a high end processor for the extra work to compress and decompress not to be a bottleneck,.

There are different types of compression in general I see no reason to not apply row compression on any SQL system

If you are hosting on azure then reducing the database size, the iops and the time and space for back ups will pay off, on premise its likely for a database your size that the server is not challenged.

Flash drives are a good option for things like reports, operating system etc.

Was this reply helpful? Yes No
Hassan_rulz 3,801 on at

Like (0)

Report
Copy link

Link copied!

Thank you all for the replies.

I will let you know if I further have any question regarding this.

Was this reply helpful? Yes No
Mahmoud Hakim 17,887 on at

Like (0)

Report
Copy link

Link copied!

HI Hassan

if this answers are sufficient , please close this thread

Was this reply helpful? Yes No