Your Dynamics AX SQL Server May Not Be Taking Advantage Of It’s Full Potential.

Dynamics AX , Infrastructure Add comments

Author John Boehm

Microsoft Certified Technology Specialist
Senior Information Systems Consultant-Dynamics AX More ...

Most SQL administrators will go to great lengths to make sure that they have the latest updates and patches on their SQL servers. As Microsoft updates the query processor in SQL, you are able to take advantage of added performance from these updates; however, this may not be true for your SQL server.  Why do I say this?

Since SQL Server 2000 Service Pack 3, the Microsoft SQL Server Query Processor Team decided to make a change in policy that affects the way that hotfixes work with SQL.  The policy is “that except for bug fixes that can cause incorrect results or corruption-all hotfixes are turned off by default”.  Although this has be the policy for a long time, I have found that most SQL administrators are unaware of this policy.  Because of this policy, many SQL servers are not running at their full potential.

The obvious question is “How do I fix this?”  The answer is quite simple. Turn on the SQL trace flag 4199.
Microsoft has a KB for Trace Flag 4199 at: http://support.microsoft.com/kb/974006 which will further explain the trace flag and what it does.

Another trace flag that should be set for Dynamics AX is the 2371 trace flag.  This flag reduces the threshold for auto-update statistics for tables that contain more than 25,000 rows.  Other SQL performance considerations for Dynamics AX are:

  1. Verify that SQL Server is configured to run as a background service in Windows.
  2. Configuring max degree of parallelism
  3. Configuring max server memory
  4. Monitoring available memory
  5. Allocating storage for tempdb
  6. Configure your Microsoft Database
  7. Plan your database storage
  8. Configure your physical storage

See this Microsoft article to learn how to configure the above options: http://technet.microsoft.com/en-us/library/dd309734.aspx

Happy Tuning!

Author John Boehm

Microsoft Certified Technology Specialist
Senior Information Systems Consultant-Dynamics AX More ...


Microsoft Dynamics AX is a robust ERP and Lean manufacturing enabler. The latest release, Dynamics AX 2012, combines the powerful planning and execution features required of comprehensive ERP integrated with the features of Lean to assist the "blended" manufacturer. Contact Agility Business Solutions, Inc. to learn more.

 

0 response s to “Your Dynamics AX SQL Server May Not Be Taking Advantage Of It’s Full Potential. ”

Leave a Reply

Leave this field empty:

Powered by Mango Blog. Design and Icons by N.Design Studio