Your SEO optimized title page contents

DynamicsPerf 2.0 Feedback

We would love to hear from everyone that installs DynamicsPerf 2.0.  Please leave your feedback in the comments of this post.  Please add the following:

  1. Dynamics Product/Version
  2. SQL Version
  3. How has DynamicsPerf helped you?
  4. What would you like to see added next?

Please post any defects on the ISSUES page of the DynamicsPerf on Codeplex site.


Rod “Hotrod” Hansen

Comments (22)
  1. Greg Y says:

    Can you explain the differences between DynamicsPerf 2.0 and Intelligent Data Management Framework (IDMF)? As a DBA supporting Dynamics AX, when would I choose one over the other?

    1. Hi Greg,

      The way I see it DynamicsPerf is much more oriented around the Performance Troubleshooting. It allows you not only to collect aggregated data from SQL DMVs, and also some configuration from Dynamics AX application, but it also provides great queries to find expensive queries, Missing indexes to improve performance.

      IDMF and Dynamics can both help you to visualize the table size and growth (Data vs index), but only IDMF will provide the Clean Up and Archive features.


  2. pkpeterson says:

    We have loaded Dynamicsperf 2.0 and are having issues with the size of the DynamicsPerf database. Our DynamicsPerf database is 179GB in size. So we disabled all jobs until we could determine what was going on. We traced this to the “Query” tables. They all had retention days of 999. We have only been running for a little less than a month so I changed the retention days for these tables to 14 days. What puzzles me is how the purge is executed. I can’t seem to find it reference in any of the stored procedures. We are running AX 21012 R3 and sql server 2012

    1. Jerry Wong says:

      I also face to the same issue.
      I install DynamicsPerf on a development environment and database DynamicsPerf grow up to 60GB after 24days.

      1. Hello Jerry,

        The RTM version 2.0 of the tool DynamicsPerf is going under final testing and should be made available very soon. Please watch this blog for the announcement.

        Thank you for your understanding,

  3. Pierre Drion says:

    Hello Rod,

    I tried to use last version of dynamics perf on 2 environments :

    – AX2012 R3, SQL enterprise version 12.0.2548.0 (testing purpose), beta 5-> having issue to get the job to collect AOS data.
    Error is the following :
    Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 25965
    The specified ‘@subsystem’ is invalid (valid values are returned by sp_enum_sqlagent_subsystems).
    Not sure if the ActiveScripting functionality has been removed in that version, or if I need to setup something more ?

    – AX4.0, SQL2008 Std (10.50.1617.0), beta 4 ,
    We had performances issues in live environement, DynPerf worked well to help and identify heavy queries and parameter sniffing…
    I had some unsupported SQL stuff, I had to slightly modify DYNPER_COLLECT_QUERY_STATS because following was not possible :
    …SUM ( total_rows),SUM ( last_rows),MAX ( max_rows), MIN ( min_rows)… from dm_exec_query_stats

    Thanks and regards,


    1. Rod Hansen says:

      VBScript is not supported on SQL 2016. Planning on updating that job to Powershell at some point.

      Don’t support SQL 2008 for your second instance. You changed to the correct sproc to fix that version.

  4. I tried to uninstall the DynamcisPerf 2.0 beta 4 by:
    – Deleting the DynamicsPerf DB
    – Running the script “Uninstall Dynperf SQL Jobs.sql” from the installation solution

    But it looks like there are still few things left. For example under Management > Extended events > Sessions > DYNPERF_* (3x)

    Can I just leave them or should I delete them manually?
    Are there other places I should check and delete?


  5. Jason Singh says:

    Hi Rod,

    I’ve had to make the following changes for creation on Linked Servers with Named Instances (non-default):

    — Named Instance
    EXEC master.dbo.sp_addlinkedserver
    @server = N’SERVER\INSTANCE’, — Change SQL_NAME_HERE with this on all subsequent scripts below
    @srvproduct=N’SQL Server’ ;

    Also update the rest of that script with the named instance in the following sections of the same script.

  6. Hi guys,

    Is there a guide how to do a clean uninstall of DynamicsPerf?
    I’d like to remove it from our sandbox and install a new version (beta 4).


    1. Rod Hansen says:

      Beta 5 has just been posted. You can delete your DynamicsPerf database. After that, there is a Uninstall Script in DynamicsPerf 2.0 Installation project that will uninstall all of the old SQL Agent Jobs.

      1. Thanks Rod. Will give ti a go this week.

  7. Simon says:

    Hello Team,

    I am starting to use this tool in my AX environment, I followed the deployment guide to get this tool installed, but I have problem to find the document to teach me how to use this tool properly. Can you please send me the link or document to show users how to use it.


  8. Peter Rutter says:

    Hi Rod,
    I’ve found a couple of problems with the DynamicsPerf purge functionality, I’ve logged specifics on the Codeplex site.
    Otherwise so far it’s a great tool.
    Many thanks!

  9. Sean says:


    I have a feeling that SP_PurgeState is incorrect when deleting the Index tables. Why are you moving the Purge Date two years in the past when deleting the Index Detail table. This is not deleting records in our environment and has pushed our DynPerf DB to ~25GB. Can you please let me know if any changes need to be made.


  10. Rod Hansen says:

    the SSRS is fixed in the next build.  Thank you for the feedback.  I've added an additional column in SSRS_CONFIG table for DATABASE_NAME.  

  11. Björn Olievier says:

    The job DYNPERF_COLLECT_SSRS_EXECUTIONLOG assumes the SSRS DB is called "ReportServer". In my case it was not and the job failed with an error:

    OLE DB provider "SQLNCLI11" for linked server "<remote_server>" returned message "Deferred prepare could not be completed.".

    I changed the statement from:

    FROM ReportServer.dbo.ExecutionLogStorage EL WITH(NOLOCK)

    LEFT OUTER JOIN ReportServer.dbo.Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)


    FROM ReportServer_AXPROD.dbo.ExecutionLogStorage EL WITH(NOLOCK)

    LEFT OUTER JOIN ReportServer_AXPROD.dbo.Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)

    This works fine for this instance. I'm not sure if there's a way to make it configurable.

  12. Rod "Hotrod" Hansen says:

    updated xpo's have been posted to the download site.  Please verify that fixes the server_name issue.

  13. Denis says:


    It seems the following code for AOTExport2012_Direct is incorrect in case Perf database is located on remote server

    public boolean getFromDialog()


       boolean ret;

       ret = super();

       sqlInstanceName = dialogSQLInstanceName.value();

       return ret;


    this will cause that [SERVER_NAME] in tables [DynamicsPerf].[dbo].[AX_TABLE_DETAIL] and  [DynamicsPerf].[dbo].[AX_INDEX_DETAIL] will be Perf server name instead actual DB server name.  

  14. Robert ODonnell says:

    Are there any plans to create some Dynamics GP specific features?

    1. Hi Robert,

      No there is no plan to create specific queries for Dynamics SL, Dynamics GP but I think most of the logic for the SQL queries and indexes is relevant for any business application that relies on SQL Server. If you have specific requests, you can add them to the CodePLex site where DynamicsPerf is located.

      Thank you,

Comments are closed.

Skip to main content