SQL perf issue - configure server correctly first or troubleshoot issues one by one?

Question

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?

Answer 1

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
  • use known DMV queries (I tend to like the Glenn Berry scripts)
  • log dmv data using DynamicsPerf If you contact support this will be the first tool they'll have you install
  • log slow queries to the SQL Statement trace log so you can relate them to the call stack in code
• review known issues in the application set up (number sequences, database log, ...)
  • look at the top 10 issues discovered by the Premier Field Engineers

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.

Answer 2

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.