SQL perf issue - configure server correctly first or troubleshoot issues one by one?
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.