web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Finance | Project Operations, Human Resources, ...
Suggested Answer

Testing Impact of Recommended Indexes

(5) ShareShare
ReportReport
Posted on by 10
Hi,

we run an on-prem version of Dynamics AX. We would like to improve performance by implementing some of the recommendations from the Missing Index report generated by SQL Server Management Studio. 

Our problem is that once we have selected a specific index to implement based on its overall score and our own judgement we want to be able to develop a test plan to measure how adding this index speeds up a specific user operation inside AX (e.g posting a journal, regenerating the trail balance, or oping the sales line detail report).

In order to measure the before and after impact of adding the index we first need to identify which AX operations or user actions need the index. We do not know how to do this!

Has anybody managed to do this?

Thanks




 
Categories:
I have the same question (0)
  • Suggested answer
    AS-21081411-0 Profile Picture
    30 on at

    Firstly note that this is going to be iterative process.

    I would probably start with a trace rather than directly creating the index based on the SQL Server recommendation.

    Run the specific AX/D365 operation that is showing the performance issue and capture the SQL generated by it. From the trace, identify the query that is taking the most time or doing a high number of reads, and then check whether it is related to the table/columns mentioned in the missing-index recommendation.

    I would then take that query and run it in SSMS against a representative non-production database with the Actual Execution Plan enabled. Capture the baseline metrics such as logical reads, CPU time, elapsed time, and whether SQL Server is doing a scan or seek.

    After that, create the proposed index in the test environment and run the same query/AX process again. Compare the execution plan and performance before and after the index.

    So essentially:

    Trace AX transaction → identify expensive SQL query → review execution plan → establish baseline → test proposed index → compare results.

    I would not implement a missing-index recommendation just because SQL Server is suggesting it. The recommendation is a good starting point, but you also need to consider existing/overlapping indexes, index maintenance and write overhead, and whether the index actually benefits the specific AX transaction you're trying to improve.

    That gives you a much more reliable answer than simply saying "SQL Server recommends this index, so we should create it."

  • Anton Venter Profile Picture
    21,173 Super User 2026 Season 2 on at

    @AS-21081411-0 

    Thank you for your reply on this forum. Is your reply created using AI? It seems to be the case. Can you confirm? 

    If using AI to answer posts, please remember to enable the "Mark reply as AI-assisted" checkbox before posting so that the source is clear to everyone.

  • Martin Dráb Profile Picture
    240,534 Most Valuable Professional on at

    @CU31121138-0 Which version of AX are you using? Not all options exist in all versions.

     

    If you start with Missing Index report, you can get the actual queries from sys.dm_db_missing_index_group_stats_query view. It won't tell you where the query was used in AX, but sometimes the query is specific enough that you can find it in the application (e.g. by cross-references on a field used in a filter), decide that it was a search in a form or so. Often it's not that simple, but let's talk about additional steps when you clarify the AX version.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 341 Most Valuable Professional

#2
CU10121822-0 Profile Picture

CU10121822-0 312

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 262 Super User 2026 Season 2

Last 30 days Overall leaderboard

Product updates

Dynamics 365 release plans