Adeel Rehman's Blog

All about what's going around me…

Dynamics AX – Tweaks to get better performance

with 3 comments

Recently we got a chance to migrate one of our Dynamics AX client to private cloud, for this purpose I was looking at benchmarks and best practices that one should follow. Following is the extract from various articles, blogs & sites. It varies from client to client and depends on number of users, hardware and various other factors.

We are only considering settings for SQL server 2008 R2 on Windows server 2008 R2, there may be some more recommendations for other environments.

Note: To reduce the length of the blog, I am not mentioning “How tos?” in this post.

Physical Storage
–        Data files and transaction log files should be on separate physical stores.
–        Tempdb files should not be on RAID 5 (RAID 1, 0+1, or 10 preferred).
–        AX transaction log file should not be on RAID 5 (RAID 1, 0+1, or 10 preferred).
–        AX data files should not be on RAID 5 (RAID 0+1, or 10 preferred).
–        Other database files (if any, such as databases for performance monitoring) should be on separate physical store from AX and Tempdb database files.


Temp DB
–        Determine TempDB size and allocate it, need to avoid auto grow. Single processor should have one TempDB file.
–        Move TempDB data file and log file to high-speed storage. Re-size data file proportionally to total size and resize log file to required size.
–        Review tempdb data and log files to ensure that they are all sized correctly and data files remain of equal size.


DB Configuration
–        Set compatibility level to 100
–        Set Read-Committed Snapshot Isolation = true
–        Set Auto-Create Statistics, Auto Update Statistics = True
–        Set Auto Update Statistics Asynchronously = FALSE
–        Ensure Auto Shrink = False.
–        Auto grow should be configured in MB 100 or 500 MB instead of % for both data and log files.


System:
–        Set “Adjust for best performance of” parameter to “Background services”.
–        Run SQL Server service under a Domain Account with minimum necessary privileges. [This is b/c of security and to avoid any hacking]
–        Enable TCP/IP network protocol. It may already be enable.
–        Disable Hyper threading.
–        Set “Max Degree of Parallelism (MAXDOP)” to 1 i.e. disable it.

Disclaimer: these are just my findings through reading different blogs and articles, anyone may agree or disagree with me and can have different opinion.

References:                
http://blogs.msdn.com
http://technet.microsoft.com/en-us/library/cc966425.aspx#XSLTsection133121120120
http://www.techrepublic.com/blog/datacenter/capturing-sql-server-2005-database-file-size-information/292
http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/
http://technet.microsoft.com/en-us/library/ee355047(AX.50).aspx
http://technet.microsoft.com/en-us/library/cc966534.aspx
http://sqlcat.com/sqlcat/b/top10lists/archive/2010/05/20/top-tips-for-maximizing-the-performance-amp-scalability-of-dynamics-ax-2009-systems-on-sql-server-2008.aspx
http://technet.microsoft.com/en-us/library/cc966414.aspx
http://blogs.msdn.com/b/ukax/archive/2009/09/29/sizing-tips-on-ax-2009.aspx

Written by Adeel Rehman

April 15, 2012 at 2:14 AM

Posted in Cloud, Dynamics AX

Tagged with ,

3 Responses

Subscribe to comments with RSS.

  1. I do not agree with disabling hyperthreading at the database level – modern processors + SQL Server have no issue with hyperthreading (which was not the case maybe 5-10 years ago)

    Allen McGuire

    December 19, 2013 at 2:22 AM

    • Allen,
      Thank you for your valuable input, I will try my best to check this ASAP with Dynamics AX and update the blog.

      Thanks again.

      Adeel Rehman

      December 22, 2013 at 12:26 AM

      • Allen,
        You are right in saying that modern processors & SQL server have no issues with hyper-threading but it is still recommended to keep it disable with Dynamics AX 2009 & 2012. I also verified it from TechNet.

        Thank you again for your input, much appreciated.

        -Adeel

        Adeel Rehman

        December 22, 2013 at 12:49 AM


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: