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 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:
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:
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