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.

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

:

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