Review: "Dynamics AX Performance Optimization Guide"

I hope everyone is doing well this fine Friday! I wanted to spend time today, giving you my insights into a recently published book. That being the title of "Dynamics AX Performance Optimization Guide."



First let me say that the depth on the database & query optimization that is given within the pages of this book is worth the purchase. The focus around optimizing the database, for sure is the right focus. I would say, in the field, 80% of the performance issues I've ran across relate directly to a Dynamics AX instance SQL Server. Further, 60-70% of these performance issues identified, usually relate back to indexes. This could be anything from missing indexes, or not having indexes properly configured and covering for where clauses of select statements.

This book goes into good detail around specific settings, how to trace down resource bottlenecks for a Dynamics AX database and more. It goes into the most commonly used Dynamic Managed Views (DMVs). Further it makes a solid attempt actually on trying to address the virutalization side of performance as it relates to Dynamics AX. Wait types, addressing blocking and other such common performance issues are well addressed in this book.



Now that being stated, like all reviews it can't all be good. You can tell that both Martin Zhen & Daniel Liao have spent time in the trenches chasing down resource vampires. Still even with my recommendation of buying this book already, it lacks in several area's.

First, section 3.11 on page 102 titled "Offload Reporting (SSRS)" is a mere nod at the point of reporting and it's impact around performance. It's a single paragraph, that honestly should have just been left off. There is no depth to this point, and it adds little value to the reader wanting to understand this very important topic.

Finally, it lacks in the focus of performance making use of SQL Server 2012 can bring, and further with it's Always On technology. I would also like to have seen mention around InformationSource. I realize however you can't cover every topic around performance or the book would be about 10 times the size that it currently represents.

Final verdict on this book, 3 out of 5 stars. It's for sure got some really great nuggets of information. It's not just for administrator / DBA / consultant types either. It would benefit well that a Dynamics AX developer, read and understand the contents of this book. It would help them in being able to grasp what their code is doing to the actual underlying data layer from X++ code. Further, helping put forth the practice of using the trace parser when developing anything for Dynamics AX.

Well that is all for this review, many thanks to Martin Zhen & Daniel Liao for completing a very nice book, that helps address and give insight on the majority of where performance issues live - The Dynamics AX Database!Til Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

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!"