Thursday, September 17, 2009

Troubleshoot Dynamics AX Performance with SQL Profiler

Issue: 
Recently, we had an issue with a production schedule report running in Dynamics AX PROD Environment, which was taking around more than 30-40 mins to display on the screen. The same report used to come in 2 mins or 3 mins time before. This is a critical running report for the business and a performance delay on this report means a huge impact on the business. 
We initially tried with AOS/SQL restart but still this issue used to pop-up every 2 days in a week and since this is a 24 * 7 system, we didn't have the luxury to do a SQL/AOS Restart as it impacts negatively production hours. 
Mentioned below, are a series of steps which we followed to resolve this issue.
  
Troubleshooting Steps: 
Using Code Profiler (20%)
i. Walk through the code for the report using Code Profiler Tool available in Dynamics AX. This is a handy tool to start with, when debugging performance issues. To know more about Code Profiler, read these links:
This helped to find out the object methods(standard and customized) ones which were taking the longest duration to execute.

ii. With the aid of code profiler and anlaysing the report code, helped us in the following: 
    - Removed some redundant code from report methods
    - Re-wrote one report method as table methods

   This helped us in achieving 20% + in performance, The rest 80% (the considerable part) of the performance improvement came in form of adding indexes, defragmentation of indexes and updating statistics on  the table (Thanks to SQL Profiler), which is demonstrate in the next steps
Using SQL Profiler (80%)
Using SQL Profiler, helped us in the following:
     - Created missing indexes on some of the AX tables used for the report 
     - Rebuilt and Reorganized some of the indexes
     - Update statistics on the tables used for the report 
To know more about SQL Profiler (Introduction), these links should be handy:
i. Created a new SQL Profiler Trace Template for troubleshooting production schedule report  based on the Duration and Tuning default templates and filtered the report columns for database and Application name as Microsoft Dynamics AX shown below: 

 ii. Adding the filters

  
iii.  Saved the trace to a table to enable troubleshooting on the queries which was taking longer duration. 
iv.  Queried the Trace table to extract long running queries and the table objects associated with it. The below Execution plans show the improvement in queries before and after indexing for each of the tables. 
      Also, to be noted below in the execution plans are the index lookup, Initially the lookup used to be from RID or bookmark lookup, but after indexing, the scan is done using the index lookups which
      overall improves the query performance.
     Below are the 3 table objects, identified by SQL profiler which needed some additional indexes. The below graphical plan can be generated in SQL Query Analyzer by using the Show Estimated Plan (this comes very handy when it comes to understand more from an index point of view, which index is benefitting the query and which is not)
     Custom Table (Before Indexing)    
  
 After Adding Index
SalesLine Table (Before Index) 

SalesLine Table (After Index)
ProdTable (Before Index)
ProdTable (After Index)
In all the above cases, one can see that Estimated cost of the query has come down after adding the required indexes.
Estimated Subtree Cost is generated via the Query Execution plan which tells us the total cost to the query optimizer for executing all the operations within the same subtree. The lower the cost the better for the performance of the query. 
Run Rebuilt and Update Statistics Queries for the following tables: (Initially, run this in your TEST/UAT environment which has a similar data load (mimics to PROD), this will help in analyzing the performance results before running it in PROD.

 
Performance Metrics (Before Adding new index)

TablesDuration (microseconds/seconds)Estimated SubTree Cost
ProdTable976664/0.970.5911
SalesLine8381053/8.3810.200
MPRPPCComponents1158199/1.154.8435
Performance Metrics (After Adding new index)

TablesDuration (microseconds/seconds)Estimated SubTree Cost
ProdTable20439/0.020.0038
SalesLine1104950/1.100.7177
MPRPPCComponents50442/0.050.0068
Note – Duration column here is the time taken in microseconds by long running queries in the report which uses these tables. So the lower the duration the better the query efficiency.
Similarly, Estimated Subtree Cost is generated via the Query Execution plan which tells us the total cost to the query optimizer for executing all the operations within the same subtree. The lower the cost the better for the performance of the query.
 Performance metrics (Before & After Index Defragmentation – Rebuilt and Reorganize indexes): 
Before:

Tables

Average Fragmentation on all Indexes
(%)

Salesline

92.68

InventSum

114.29

InventDim

164.04

ProdTable

264.46

InventTable

280.49

MPRPPCComponents

127.50
 After:

Tables

Average Fragmentation on all Indexes
(%)

Salesline

11.22

InventSum

0.61

InventDim

4.42

ProdTable

15.68

InventTable

7.73

MPRPPCComponents

0.52
 Note – Average Fragmentation % here is the fragmentation percentage (out-of-order pages in index or heap). Some additional notes on this - SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. 
One can refer SQL Books Online.chm to delve more into Query Execution Plan, Index Fragmentation etc. They have a detailed explanation about each topic. Also, there are very good links to resources on www
To conclude, SQL Server Profiler is a very handy tool which has come to my rescue number of times, when troubleshooting performance issues with Dynamics AX. Also, I would suggest to go through some valuable posts on performance by Brandon  in his blog. There are series of posts which talks about perfromance issues 

Saturday, September 5, 2009

Create a Dynamics AX 2009 AIF WCF Web Service

This video talks about AIF in Dynamics AX 2009. This is a good video for a beginner to get started with creating AIF Services

It shows an example of how to:
a. Create a Service
b. Add new operations to the Service
c. Extend the service
d. Deploy the service
e. Consume the service


http://beta.channel9.msdn.com/posts/SanjayJain/Microsoft-Dynamics-AX-2009-AIF-Web-Services-Screencast/

Google Wave and SAP Enterprise Services

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/15521%3Fpage%3Dlast%26x-order%3Ddate

Thinking Loud

Will Google Wave + Chrome + SAP Enterprise Services in a combination form a killer app (at Enterprise level) and give a tough competition to Dynamics AX and Sharepoint technologies? Only time can tell...

Wednesday, September 2, 2009

Consume Service References in Dynamics AX 2009

Here's an interesting post by Brandon talking about consuming service references in Dynamics AX 2009. He goes a further deep into namespaces and the benefit of .Net namespace and intellisense when you use with X++. Thanks Brandon for the post

http://dynamics-ax.blogspot.com/2009/08/consuming-service-references-in.html

Watch this video on X++ Translator to Managed Code

Introduction

Thanks to Peter and team for this great video. In a brief, this gives us an insight about the future roadmap for X++. Watch out this video. Please see the link below the extract..

Dynamics Program Manager Peter Villadsen and Software Developer Gustavo Plancarte teach us about a new tool they've developed that translates X++ byte code into MSIL. We learn a lot of history along the way and gain insights into the process of taking X++ into the .NET age.


Microsoft Dynamics features a proprietary language called X++ (basically a superset of Java, with some strong data primitives added) and a complete stack (compiler, interpreter and debugger) that goes with it. The new feature Peter and team have developed is a tool to generate managed code from the X++ intermediate language produced by the X++ compiler. This will have profound impact on the performance of the business applications written in X++, and it very clearly points to where they'll be going in the next few releases of Dynamics Ax.

http://beta.channel9.msdn.com/posts/Charles/Peter-Villadsen-and-Gustavo-Plancarte-Inside-Ax-Translator-X-to-MSIL/

My Thoughts

Downloaded this 30-min video and here are some of my thoughts after going through it:

a. Start learning .Net Fx, C#, LINQ to hold your jobs ;-)
b. In my opinion, I feel X++ is still a good DSL (Domain Specific Language) closely written and tightly integrated with the business modules and C# or VB.Net should be used as the client interaction language to write user interfaces or client logic. This is possible via the translator which can translate pcode from X++ to IL.
c. Pete rightly says that the Morphx IDE is more suited for RAD developers compared to the current Visual Studio IDE and I completely agree with him. In my experience as a RAD Developer, I found the Classic Visual Basic IDE (Visual Studio 6.0) and the MorphX IDE (in Axapta 3.0) which gives the best developer RAD Experience.
d. In the future as the roadmap would be to migrate all the codebase to .Net, it would be advantageous  for the upcoming generation of developers or technology consultants for Dynamics AX ERP as they need not worry about learning multiple languages i.e. X++ and .Net, they have to just invest in one language. Also, there won't be shortage of resources as .Net skills are available in abundant in the market.
e. It would be interesting to see how this translator evolves into the mainstream product and when it will be released