page contents

DynamicsPerf needs your HELP !!


We REALLY, REALLY, REALLY want to hear from you !!!

I need your help in defining a list of new features for DynamicsPerf.  Please leave comments below explaining what you would like to see and what impact that feature would have on the Dynamics community. 

 

Here are a couple of items on my agenda to add:

  • Multiple server support

We still have to collect locally on each SQL Server but want to add a way to combine all of that into a singular DynamicsPerf database.

  • Extended Events

There is a lot of magical performance data in extended events.

  • More SSRS reports

We gladly take any contributions from the Dynamics community on this one

 

Thank you for contributing ideas to the DynamicsPerf toolset !!!

 

Rod “Hotrod” Hansen

Sr. Premier Field Engineer Dynamics AX


Comments (14)
  1. AX Tech says:

    Hi,

    Dynamics Performance Analayzer has matured and it's a useful part of my Tool Box. As others has stated, it's quite labor (and SQL resource) intensive with it's script based approach. I would like a console or GUI to show some Dashboards and reports more easily, since at least I tend to end up with extracting key information to Excel for visualization/presentation.

    The SSRS reports provided is less useful and some of them are not working out of the box.

    The best part is the correlation between a statement and the corresponding Execution Plan With the possibilities to search for specifics within the Execution Plan.

    All in all a good tool for capturing how the application (AX for my part) is working with SQL Server.

  2. Martin says:

    Hi Rod,

    I just tried to use this for the first time; here is my only recommendation: please make this more user friendly - one script to run and a html report as a result - or, perhaps something more in the direction of TraceParser, rather than many scripts... That way, all of my customers would be able to do the basic performance troubleshooting themselves.

    Thanks,

    Martin

  3. Marco says:

    Vadim,

    could you explain your solution "log tts* commands" a Little bit more detailled please. We are using AX2009 and I think it could be a nice addon for us.

    Best regards,

    Marco

  4. Pierre Drion says:

    Hello Rod,

    Extended events and SSRS reports are definitively 2 things that would be nice.

    I would maybe also think of some specific oriented tools :

    • some PFE recommand to have a look on data compression feature, but i did not find real support included in DynPerf. I think some basic storedprocedures & queries might be included in the tool in order to get the status on compression and get some  recommendations based on index statistics (read vs updates) and index size : estimated gain by compression, page vs row compression recommandation, ...
    • Index selectivity & efficiency : One of the most changing element between companies can be the data and the way they model their processes. Their process and business have impact on data repartition in tables. So in my experience parameter sniffing issue is not only about DataareaId but could also for example be linked to inventdim. More over, data repartition could be a base to improve efficiency of existing indexes by reordering columns, and not only suggesting new indexes.

    • sometimes, i have issues with pre-build views because they use date and time of the datacollection to join tables. Depending on the run, it happens the joins is not done correctly because there is time difference between the records of the 2 tables (very small difference, cannot remember if it is second or milisecond). Might be a good idea to use another join critteria, or to change the script in order to make sure to keep the same collection time for all 'sub scripts'

    Best regards,

    Pierre.

  5. Rod Hansen says:

    Vadim,  Thank you for your feedback !!  

    -Blocking  If you enable ConnectionContext, technet.microsoft.com/.../hh699644.aspx , and enable the Dynperf_Optional_Polling_for_Blocks, you will be able to see the AX User id in the data.  The optional blocking job populates a table called BLOCKS in the DynamicsPerf database.  This will go along ways towards resolving the problem you are discussing.  I have some special things with Extended Events that I'm working on to solve this issue.  

    -TempDb  I agree with you on this one.  It's been on my radar scope to add because of the very things your discussed.

    -SQL2005 Is for sure not going to be added into this version.  Everything is built around grouping query data by QUERY_HASH and QUERY_PLAN_HASH which do not exist in SQL 2005.  We do have an older 2005 version that we have sent out on request. If we get enough feedback or requests for this, I might be convinced to post that older version on the download site.  Although, AX 4 did support SQL 2008 late in its life which would allow you to use the current version.

    I'm glad you mentioned that you use this tool on non-Dynamics databases.  The core code for sure will work and you can do the same type of analysis.  

  6. Vadim says:

    Hey Rod,

    a big thank you and the Perf team for providing us with such a great tool. I've been using since a couple of years (even for non-Dynamics environments!) and I like it very much!

    Here's a couple of things I would like to have included:

    • SQL2005 support, at least a partial support. I know, SQL2005 is not supported anymore, but there are still many customers around who are using SQL2005 (and AX4 for that matter) and for various reasons are not be able to upgrade
    • TempDB usage statistics. Many perf issues stem from the fact, that SQL is spilling to tempdb or using inadequate execution plans (for example by using hash joins for small amounts of data). Sometimes tempdb problems arise due to simply wrong configuration or slow IO subsystems. Sometimes I see issues with the version store (to much data, slow ghost cleanups, etc). Having a good TempDB usage overview would be helpful

    • Blocking

    this is a serious one. The main problem we have is that we often don't know who the blocker is: while we see the <LAST> SQL statement of the blocker, this is very often NOT the query which is responsible for the blocking of particular object. So it is often very difficult to find the exact piece of code, which is responsible for blocking. We helpled ourselves by tracing this information from AX (by logging TTS* commands above certain thresholds). It would be great if you guys could implement this tracing somehow in Standard Ax

    Thank you!

    Vadim

  7. Rod Hansen says:

    JP,  Is the challenge around installation or understanding of the data?  You can find the installation here: blogs.msdn.com/.../performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide.aspx  .   We do provide a flowchart of which scripts to look at in which order as part of the solution.  You will find it in the root of the DynamicsPerf folder or under MISC in the solution.  There is also a recommended reading document that has several books and blogs that will give you knowledge for understanding the data we collect as well.  For those on Premier support, we offer a Performance Hands on Lab service where we come onsite for a week and walk you through all of it.  I would love to write a book on this subject but it would probably turn into 5000 pages with as much information as I would have to put into it. In the mean time, I'll be writing this blog series trying to breakdown as much of it as possible into smaller consumable chunks of information. If there is a topic you would specifically like covered please ask here in the comments.  

    Let me give an example of where it can be really hard to interpret the data which is also why there is such a long comment before the script.  We have a missing indexes query that shows you where SQL Server has suggested a new index to improve performance.  There is one large assumption on SQLs part in determining this out and that is the SQL statement is written correct.  That is impossible for an application or script to figure out.  It requires 2 big pieces of information.  The technical part is making sure that SQL is correct and that you don't already have a similar index.  But the harder part is figuring out if the suggestion actually matches the intention of the developers code and if that actually resolves the business problem correctly.  This is the biggest challenge in writing documentation to understand the data because it goes beyond just technical.  One of the posts in this series will cover missing indexes and I'll have more details in that post. Also, if you ever find yourself working with somebody who suggests adding more then 100 indexes to your solution using a script from the missing indexes dmv, run away quickly.

    I would like to do some video documentation at some point.  I'm really looking at Office Mix for this type of content.

    One other note, I'll be at Convergence, so anybody coming to Convergence, feel free to look me up and I'll be glad to talk.

    Thanks for the comments and keep providing your feedback !!  

  8. JP says:

    A good documentation, so that we understand how to use it...

  9. Anil says:

    Good to know, thanks Rod!

  10. Rod Hansen says:

    Anil, If you enable connectioncontext and use the optional blocking job, the AX User id will be part of that blocking data.  This sounds like a good article for me to add so the DynamicsPerf Deep Dive series.

  11. Anil says:

    Would it be possible leverage the use of the SPID tracing feature so that the blocking data also contains the userid of the blocker and blocked sessions?

  12. Rod Hansen says:

    Andrew, Would color coding some of the SSRS reports work?  Obviously, with a legend to explain the color coding.  This is relatively easy to do for basic yes/no type things.  Everything else requires a lot more analysis and information about the business to answer.

    There is overlap between LCS, RAP as a Service (Healthchecks) and DynamicsPerf.  Keep in mind that these other things were built on top of the idea of DynamicsPerf which was started more then 8 years ago.

    Toni,  Have you looked at the SSRS reports we provide?  The monitoring row count reports are probably the closest ones we have to a dashboard type report.  It shows transaction volumes within Dynamics AX. Also, what types of things would you like to see on a dashboard?

  13. Andrew Bartley says:

    I'm looking forward to more detail on this. I use DynamicsPerf (was originally trained by Graham Duncan) to analyse customers solutions and come up with recommendations.

    I've also started using Microsoft Lifecycle services to go hand in hand as they look at a lot of similar aspects. The LCS tool is nice in some ways as a lot is automated where there are a lot of manual interventions required in DynamicsPerf.

    One thing I find hard is obviously analysing the data. It's often very open to the individual’s interpretation, it would also be handy to see how you provide a report output? I generate a word document, with a traffic light system to show impact and issue severity.

    I'd also like to see some ability to monitor network latency between client/servers if possible?

    Keep up the good work

    Andrew

  14. Toni says:

    Dashboard of some indicators within AX Client

Comments are closed.

Skip to main content