Introduction

As a new member of the Kaya family I feel the urge to introduce myself. Hi I’m Boye Walters, a 34 year old developer with over 12 year experience in Microsoft Dynamics Ax and Microsoft SQL Server. Having worked with all versions of Ax ever released in the Netherlands I can truthfully say that I’ve seen a lot already in Ax. As for SQL Server I’ve actively worked with SQL2000 and above, using all features available including integration, analysis and reporting.
Now the introduction is done let’s start off with something I experienced last week at a customer.

A badly performing View

I was asked to build a view with a dozen or so datasources having inner, outer  and exist joins. To maximize performance I minimized the number of fields and put some relevant ranges in place minimizing the number of records. I also used the relations already on the tables. It should produce an ultrafast view you would think. Too bad, it took me 40 minutes to open the view in the Ax client. But after 40 minutes it showed a lot of records: 2. Because I didn’t use any business logic, user interface or what so ever, I figured it should be SQL causing the delay. As a ran the query in SQL I believed to be proven wrong again, because it ran in 4 seconds. But SQL did suggest an index that could improve performance 85%. After applying it running the query in SQL took under a second. And in Ax it also took under a second. Sounds strange? Yes. Explainable? Absolutely , because of the missing index the query requested a table lock on dbo.salesline which was heavily queried so it took a while to get that.

How did I identify the missing index?

Well after synchronizing the view in Ax I started SQL Server Management Studio (SSMS) and went to the right database and View. There I rightclicked on it and selected ‘Script view asSELECT TONew Query editor window’.

Script view asSELECT TONew Query editor window

‘Script view as – SELECT TO – New Query editor window’

 

This will generate a select script for the view.
Now instead of running it went for the execution plan. Using ‘Display Estimated Executing Plan (Ctrl+L)’ button on the top of my Management Studio. Look at the second query (first is just to select the right database), if SQL determines it is needs an index to give you the answer to your query it will say so. The way it does is by showing a create statement for the missing index in green. Like it did with me:

view02

 

Now I’m going to test it before I build it in Ax. First I rightclicked on the message and choose ‘Missing Index Details’. This opened up a new Query window with a create statement that is commented like this:

view02

I uncommented the query and gave the index a name as requested.
Now I ran this script, and the index was created in SQL. I went back to the previous script and used the ‘Display Estimated Executing Plan (Ctrl+L)’ button again, to make sure SQL didn’t suggest another missing index.
No more missing index, so I went to Ax and ran the view again. After recovering from the Wow factor (and going through several emotions: impressed, not believing, not understanding, believing, grasping the understanding and still wowed ) I created the missing index in Ax after removing it in SQL.

Why ask a program not a developer?

As many of you know, SQL Server works in a (for non SQL developers) odd way. You tell SQL Server what you want and how it should be formatted but SQL Server determines what would be the best way to deliver you what you want. That makes SQL Server the one that can tell you what it is missing to do it’s job properly. This also means you could use this method to determine what missing index would speed up a SQL query that you caught with the trace parcer. There are even queries possible in SQL Server that show you all missing indexes at ones. And that is exactly what we use in our Performance Dashboard.

Interesting links:

Technet on finding missing indexes

Subscribe to our newsletter

Comments5
  1. David RustonOctober 26, 2015   

    Dynamics AX leverages many parts of the MS Stack, and in my opinion at the base of that is SQL, propping up the whole ‘performance tower’! This kind of useful pointer article will help many consultants know to ask the ‘question’ of the technical team ‘are the indexes all fine, are you sure?’.

    • Boye WaltersOctober 26, 2015   

      Thank you for your reply and I do think you’re right David. That’s why I’m going to write some more blogposts on the subject of indexes and related performance issue’s. One I’m currently working on is index fragmentation, and another one is about how to choose the right clustered index.

      • CalvinOctober 13, 2016   

        Hi Boye, I do look forward to see your research of how to choose the right clustered index. BTW, do you think by using Rank will be helpful for dealing with the missing index situation?

  2. PraveenOctober 27, 2015   

    Very well written and an interesting article on the hidden gems to improve the performance from the perspective of AX.

Leave a Comment!

Your email address will not be published. Required fields are marked *