page contents

What’s New in DynamicsPerf 2.0 Release Candidate 0


First, I want to say “Thank You” to the community.  All of the feedback has been greatly appreciated.

 

Let’s start with SSRS data collection.

 

This is now working as designed.  There were some schema changes needed to get this working as necessary so the upgrade script will truncate the SSRS_HISTORY table and the SSRS_EXECUTIONLOG tables.  The SSRS data collection has been moved to it’s own SQL Agent Job called DYNPERF_CAPTURE_SSRS.

image

There is no additional configuration beyond setup of the SSRS_CONFIG table in the install guide.

 

Additional Options for controlling the size of the DynamicsPerf database.

 

It appears that our 5 minute data collection is working really well as we are collecting all variations of query plans.  The problem is that this is consuming a lot of space.  So in Release Candidate 0, we have added some additional configuration parameters to better control how much data is collected.  These options are a part of script 4-ConfigureDBs to Collect.sql.  The parameters are as follows:

NO. OF QUERY PLANS TO KEEP PER QUERY_HASH – This parameter limits the number of query_plan_hash values we keep per query_hash.  It defaults to 20.  The extra plans are purged with the data purge job that runs once per day so you can have more then the configured amount until that task runs.

Ignore queries below this time in (ms) – This will cause the collection to not gather query statistics on queries below this threshold.  Default is 0, collect all.

COLLECT TOP X PERECENT QUERIES BY TOTAL_ELAPSED_TIME – This applies a TOP X PERCENT to the collection of QUERY_STATS.  Default is 100, collect all

COLLECT TOP X PERCENT QUERY PLANS BY TOTAL_ELAPSED_TIME – This applies a TOP X PERCENT to the collection of QUERY_PLANS.  Default is 100, collect all

 

If you have a large SQL Server with 256GB of memory or more and a large Dynamics deployment, then you may want to adjust these values.  The recommendation would be to adjust the number of plans to keep and the TOP X PERCENT Query Plans first as these will be the biggest space savings.

 

The third major improvement for Release Candidate 0 is performance.

 

There were a few of the scripts, data purge, parse query plans and database statistics that were causing some blocking and slower performance.  There were 2 fixes implemented to resolve this issue. First, the procedures were rewritten to select into temp tables to avoid table locks from the complexity of the rules in the queries.  Secondly, we have added a new SQL Agent Job called DYNPERF_PROCESS_TASKS_LOW_PRIORITY

:

image

 

All of the longer running tasks are now part of this job.  This was done to make the project run smoother.  This change will ensure that QUERY_HISTORY rollups and QEURY_ALERTS tasks run in a timely manner.  There is a new task type called PROCESS_LP that is used as part of the scheduling tables to get the appropriate tasks to run on this new job.

 

There is no additional configuration needed for these new jobs.

 

There were many other small changes made to improve the toolset.  Some of those changes were provided by the community so “Thank You “ again.

 

Please try this new version once posted.  I look forward to hearing your feedback.  I’m especially interested in your feedback on the sample queries and what changes could be used to improve the understanding of the data.  Reporting for DynamicsPerf 2.0 will come after release.  We know Dashboards are a big request and are investigating a solution.

 

Rod “Hotrod” Hansen

Comments (11)
  1. Pal Jantvik says:

    Hi I wonder how to handle an Always on scenario. When putting in the cluster name to the "DynamicsPerf..DATABASES_2_COLLECT", it does not work. When I put in the name of the primary server, it works, but what happens when the primary Changes?
    Regards Pal

  2. Paul says:

    Hi,
    do you have plans to release the DynamicsPerf tool for the New Dynamics AX (AX7)?
    Regards,
    Paul

    1. Hi Paul,

      The new version 2.0 will be compatible with the new Dynamics AX in non production environements only, since the Lifecycle Services Telemetry will be the tool for Production instance.

      Regards,
      @BertrandCaillet

      1. Paul says:

        Thanks Bertrand for the clarification!

  3. Jago van der Most says:

    Hi Rod,
    This write-up is titled what's new in Release candidate 0. Is there any place we can get this release? (Couldn't find it on the Dynamicsperf codeplex site).
    Regards,
    Jago van der Most

    1. Hi Jago,

      Here is the download link to the DynamicsPerf 2.0 solution:
      https://dynamicsperf.codeplex.com/releases/view/619217https://dynamicsperf.codeplex.com/releases/view/619217

      Current version is Beta 5 but RTM is planned very soon, stay tune!

      Regards,
      @BertrandCaillet

  4. Greg Y says:

    Nice! Is there a link to RC0?

    1. Hi Greg,

      Here is the download link to the DynamicsPerf 2.0 solution:
      https://dynamicsperf.codeplex.com/releases/view/619217

      Current version is Beta 5 but RTM is planned very soon, stay tune!

      Regards,
      @BertrandCaillet

      1. Gabriele Solieri says:

        Hi,
        pls ... can u give us rc0 link? it's better than beta5, right? 😛

        1. Hello Gabriele,

          Please look at the "Upgrade Scripts for All Betas" that contain many fixes for your version.

          Regards,
          @BertrandCaillet

Comments are closed.

Skip to main content