6

This is for SQL Server 2012.

I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see that none of the best practices for SQL Server have been configured - MAXDOP, tempdb, trace flags specific for Dynamics, and index/fragmentation maintenance. I am pretty sure there are inefficient queries, missing or non-optimal indexes, etc.

I spent a little bit of time looking at wait stats, but now I'm wondering if I should first apply all the recommended best practices (industry-standard best practices for DynamicsAX), then troubleshoot what is slow, or dig into each issue one by one, and address it as it comes? I know some will say, don't start flipping switches until you know for sure it needs to be flipped.

How would you approach this kind of scenario?

  • 3
    Look at it this way: if you call support about performance problems, what would they have you do first? – Erik Darling Jun 15 '16 at 2:35
  • 1
    I hear what you're saying, but I've never called support before.... – Greg Jun 15 '16 at 2:59
8

There are a couple of things to say about this, Dynamics AX performance is not necessarily always caused by SQL Server slowness, especially if you say "everything is slow".

When faced with performance issues in Dynamics AX I tend to use the following approach:

  • First start with reviewing resources on all servers involved (cpu pressure, memory pressure, page life expectancy,...)
    • Every AOS, Client & Citrix need at least 4 cores
    • AX is very sensitive to CPU latency, make sure your CPU's are set to high performance in the BIOS. If you say everything is slow, even simple forms my gut feeling says this isn't the case.
    • Perform the standard checks such as disk latency and the likes, not only on the SQL Server but on all components involved.
  • Then verify if all the SQL Server settings are correct and according to best practices
    • Correct trace flags
    • Correct index/stats maintenance
    • MaxDOP
    • max memory settings
    • ...
  • Review all the relevant database properties if they are set according to best practices
    • auto update statistics
  • Review the database design by reviewing missing indexes and slow queries
  • review known issues in the application set up (number sequences, database log, ...)

Once you have a setup that works acceptably and some processes remain slow, use the Trace Parser to have exact timings on every piece of code and query that's being run during the process. This should help you either solve or at least explain why the remaining process is slow.

0

I would first configure the server according to best practices including:

  1. RCSI

  2. MAXDOP = 1

  3. TempDB

  4. Memory Settings

  5. etc...

Then if performance is still an issue, dig into the most resource intensive queries and try to optimize those...though, I believe in AX everything is cursor based.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.