Different people take different approaches, but what I find the most beneficial for most customers is the following approach:
1. Check SQL is configured correctly.
2. Check hardware is sufficient for current system requirements.
3. Check all indexed fields have statistic available.
4. Determine if the Parameter sniffing issue is relevant to your system. If so take corrective action.
5. Check the health of the current indexes, and re-build indexes that are necessary.
6. Check SQL for long running queries. (dont get to stressed on this one, I would look for queries that run for a long time and stand out as areas you either have problems with, or areas you have done lots of customizations)
7. Use targeted analysis on individual processes.
Items 1-5 and 7 are key to the system performance. 2 is important as systems change, and you need to evaluate if your current system is still running within the original design criteria, if not you may need to change your hardware configuration.
Item 7 is the main area I work in once the core system if done (1-5). Using tools like Trace Parser, SQL profiler and SQL database enging tuning this is where most of the benifit will be achieved. Target the process in AX that is causing the problem problems or having the business issues. Trace Parse this process, and review all the SQL calls. Look for un-indexed searches and poor AX code (Select * from, and loops within loops) Evaluate if these can be removed, reduced or optimized. Adjust the system, and run again, did the performance improve. Target 1-5 processes only, then release the changes to production. See how users respond.
Often small changes to indexes and code will produce big results to the process, and often to the entire system, as one are of the system is used by many other objects, so fixing one issue will help other areas of the system.
I hope this helps you move forward to improve the performance of your system.
Please tick the answers your question if you feel that it did help you.
Nigel
