Getting the most out of Dynamics AX - Performance II

Last time we talked about performnace I left off going into how performance tuning is a Dynamic string of on-going events.

Getting the most out of Dynamics AX - Performance I

"The mind set must be, it's a Dynamic set of on going events. This can't be a static thing that happens once. You don't have to deal with it everyday, but just because you get things running smooth and fast, does not mean that will last for 3-6 months. If you add a lot, change a lot, etc. then what ever is changing and adding needs to be investigated for best performance possible."

So we left off in the details of taking a look at X++ / Xpp queries. Making sure these custom implementations of data access code are not making use of nested While Select looping statments, and have covering indexes.

I also mentioned other things, but I wanted to focus this talk now on indexes. I would say, that near 80% of all major performance tuning and gains comes from adding missing indexes. Let me bold that: 80%.

The issue, even for out-of-the box tables not all required or needed indexes exist. Microsoft, nor any company, can forsee how all companies will make use of out-of-box tables.

Add custom tables, and you have the possibility for a lot of tables scans taking place, where index seeks are needed.

So how do you spot these missing indexes. A very handy SQL Server Dynamic Managed view exists. The following is a query that query this view.:


Print '*********************************************'
Print '* *'
Print '* *'
Print '* MISSING INDEXES *'
Print '* *'
Print '* *'
Print '*********************************************'



select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
order by s.avg_user_impact desc


What I find myself doing is adding to the where clause and statement like '%[Insert DAX Database here]%' which helps focus me into the given Dynamics AX database at hand.

Having this information, mixed with information feed from the Profiler, and word of mouth from the users on processes that are slow can really help identify missing indexes for a database.

Once you have some target missing indexes, you need to take these with a grain of salt. Analyze them, look at them and see if they make sense. Test them out. Then Do Not create them via SQL Server. These need to be created within Dynamics AX 2009 itself. Once they are, re-run the missing indexes query above, and your index will no longer be in the list.

A word of advice here, this shows the missing indexes, number of compiles, etc. from the last time the SQL Server Service was shut down. So if your SQL Server was recently shut down, this information will not be to valid, until a full day of processing has ran.

On top of the Missing indexes DMV, there are plenty more area's to look at for SQL Server based performance.

What you want to keep in mind is that the point of clustered and non-clustered, covering indexes, exist to make the result set and look up time by the SQL Server engine reduced.

If you have a select statement, with a where clause and no index exists that has those where clause columns and table scan will be ran. That is most costly. However if you have a clustered index (physical ordering of the table and page files) and then covering indexes, the engine can perform an index seek. That is the desired approach, to be able to look at a query plan for a compiled, executed query and see index seeks across the board.

Alright, in the next part of this series we will continue with getting the most out of your Dynamics AX 2009 instance. Next time we focus on the AOS!

Check back soon, as more great post are to follow!




"Visit the Dynamics AX Community Page today!"