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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Missing Index Query in...
Microsoft Dynamics AX (Archived)

Missing Index Query in DynamicsPerf database

(0) ShareShare
ReportReport
Posted on by Rohin 4,624

Hi All,

I have just installed DynamicsPerf tool with help of below link:

https://blogs.msdn.microsoft.com/axinthefield/dynamicsperf-2-0-deployment-guide/

 Now i am trying to understand how to use this tool for Dynamics AX performance. I have found one blog series for LINk  so i am executing missing index query screen shown below but its not giving me any result :

3531.SQL.PNG

Please explain me as i am new to this. 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,904 Most Valuable Professional on at

    The explanation is that you have no data meeting your given criteria. If I was you, I would also check if I have any data at all; maybe you haven't collected anything yet.

  • Rohin Profile Picture
    Rohin 4,624 on at

    okay, then how to do that , can you please explain?

  • Martin Dráb Profile Picture
    Martin Dráb 237,904 Most Valuable Professional on at

    Oh, you don't know how to write SQL queries at all? It's very simple in this case. For example, if you want to see if you have any data in MISSING_INDEXES_CURR_WV (without any filtering), use SELECT * FROM MISSING_INDEXES_CURR_WV.

  • Rohin Profile Picture
    Rohin 4,624 on at

    yes I have . I run without filter and its showing data. Next what to do . please tell

  • Martin Dráb Profile Picture
    Martin Dráb 237,904 Most Valuable Professional on at

    You asked for explanation why you don't get any data and you've got it. There are simply no records matching your filters (e.g. that AVG_LOGICAL_READS must be greater than 1000).

    I don't understand else you want to hear from us.

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    If you do have data, it is just a matter of what filter criteria do you enable or disable. They have listed a bunch, and could comment out the ones which you do not need with a double-dash before:

    -- This is a comment in T-SQL

    Default DynamicsPerf queries are mostly OK without the need to change the commented text. If nothing is returned, that query result is probably OK. Also you need to apply common sense to decide what threshold figures, filter criteria, and so on should be set for the individual values, since they did not document every fields in there.

    In your case most likely this is executing against a development system which has no data movement at all, so relying on amount of data read with a specific average wait time is not useful.

  • Rohin Profile Picture
    Rohin 4,624 on at

    Let me clear Martin :

    In simple words , I want to understand below things:

    a) what exactly does this query?

    b) how can i use this query for enhanced AX Performance ?

    c) how to set these filters ? what  are the meaning of all these fields used in filtering?

    after getting above things. i can able to use it properly. Would you be so kind if you can help me to understand with your knowledge as i am new to this area?

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    See the answer above. The tool was written by people who know SQL Server and a little bit of AX. The tool assumes SQL Server knowledge, so the best approach would be that you also read up on those.

    For example at the Microsoft Convergence conference in Atlanta 2 years back I found a great discounted book on SQL Server Query Optimization, which covers many aspects of what you could do with the perf tool. I would suggest to find similar resources, learn about how SQL Server works.

  • Rohin Profile Picture
    Rohin 4,624 on at

    well, I'll try to find those resource .

    Anyways, can you able to give answers for my above questions??

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    DynamicsPerf is feeding off SQL Server DMV information to collect data, in this case most likely from the query execution stats.

    You can find details about individual fields in the documentation (as always. use search!). There is no specific field for the average values, but it is just a simple calculation usually between 2 timeframes.

    docs.microsoft.com/.../sys-dm-exec-query-stats-transact-sql

    If you want to see from which DMV does the information come from, simply have a look under the Views within the DynamicsPerf database, modify it, and you will be (you should be) able to match it with the publicly available information.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans