UA-44032151-3 page contents

What’s New in DynamicsPerf 2.0 !!


 

What?  There is a DynamicsPerf 2.0?  It’s taken 6 months and almost 600 hours of coding time.  This upgrade really is worthy of the 2.0 version. What has changed?  EVERYTHING !!  Don’t worry it still has the same look and feel.

So, with that, let me begin to go through the new features.

 

Remote Data Collection

Yes, we can finally install the DynamicsPerf database on a server other then where your Dynamics production database resides.  This has to have been one the biggest enhancement requests that we’ve had for the toolset.   This gives everyone the flexibility to provide the data in DynamicsPerf without having to give them access to the production server.  This also means when analyzing data in DynamicsPerf that you do not consume any resources of the production database.

 

Scheduling Engine

DynamicsPerf now has it’s own scheduling engine that allows us to collect each of the data points on their own schedules.  The core stored procedure has been broken down into individual procedures that can be scheduled independently of each other.  To avoid having 100s of SQL Agent Jobs, we have added our own scheduling tables and have a couple of stored procedures that are scheduled as SQL Jobs that drive the whole process of data collection.  The big impact of this change is that now we are collecting query_stats every 5 minutes.  This also means you do not have to manually collect data when there is a problem because by the time you find out, it will have already been collected.

 

Historical Tables

One of the challenges of collecting data more often is that creates a storage problem trying to keep all that data.  To alleviate that problem, we actually aggregate query and index data into Daily and Monthly totals in history tables:  QUERY_HISTORY and INDEX_HISTORY.   This has been one of the far reaching impacts in DynamicsPerf 2.0.  First, the size of the database has trended much smaller then prior versions.  Second, you no longer have to do anything special to collect baseline data.  In fact, that job has been removed.  With the aggregated data you can compare any day to any other day and any month to any other month or compare a day to the month.  There are many new ways to slice and dice the data.  We have added many new sample queries.

 

More QUERY Data

We now collect QUERY_STATS every 5 minutes.  This makes it possible to look in 5 minute increments and see what was happening to your SQL Server.   We keep this detailed data for 2 days. This is one of the many configurable settings if you want to keep more data.   DynamicsPerf database will have the data before you find out about the problem from the users.

 

SSRS Analysis

The new version can now collect data from your SSRS server so you can track down issues that may be related to reporting.   This is always a big question that comes up when the server is under load, which is how much of it is data entry vs. reporting.  You will now be able to monitor that situation.  Ever had a situation where, “hey the report is slow”?  Is it slow because of SQL or because of something in the report, like too many subtotals or image processing?  You can now solve those issues in this version

PowerBI Reports

We have built a set of reports in PowerBI that you can include in your own dashboards.  Take advantage of this new feature to monitor your Microsoft Dynamics system !!

 

Dynamics CRM collectors

YES, we have specific collectors for Dynamics CRM in this version.  There are 3 CRM specific collectors for the Organization data, PrincipalObjectAccess, and Plugins data.  Very exciting stuff for resolving performance issues with Dynamics CRM On Prem.

 

New Data collectors

We have new collectors for :

  • Replication
  • Change Data Control
  • Change Tracking
  • SQL Plan Guides
  • and more

 

FULLTEXT Indexes

We have added fulltext indexes for the DynamicsPerf database to speed processing of some of the more challenging queries that were in the project.  You can now find all of the queries using a specific index in seconds instead of hours.

 

Hub and Spoke of DynamicsPerf datasbases

All the tables have been modified so that you can now merge data from 1 DynamicsPerf database into another DynamicsPerf database.  This is especially important for those AX Retail customers who want to track data in the local store database and then move that data to corporate later.  This is also great for everyone with a multiple database Dynamics deployment.

 

SQL Server 2016 support

We have added support for the new data added to sys.dm_exec_query_stats in SQL Server 2016.  We also provide a script for enabling QUERY_STORE on your Dynamics Database

 

Microsoft Azure support

If you have a login to your SQL Azure environment, you can collect performance data for these databases.

 

Refresh QUERY_PLANS

There is now an option to refresh the query_plans in your DynamicsPerf database.  This is great for getting the latest parameters that the query is using.  This is also great for when you may have made significant Trace Flag changes.  This will also help in updating/refreshing the parameter values for the query.  We used to keep the values from the first time we collected the query plan.  This would become a challenge months later when that data may not even exist in the database.  Refreshing of the plans solves this issue.

 

Stale QUERY_PLAN Removal

There is an option for removing old queries from the DynamicsPerf database now.  Ever change the code and have the old query stick around in the DynamicsPerf database?  This is one of the new configurable options.

 

Performance Counter data on SQL

We have added a new data collector to DynamicsPerf that collects the data from sys.os_performance_counters.  There are sample queries added to review that data.

 

QUERY_ALERTS

With the addition of the new history tables and how data is aggregated we can now do some interesting monitoring.  We have added a new process that will populate a table called QUERY_ALERTS.  This is done based on when a query’s current execution behavior is x% worse than the Day or Month for that query.  This is configurable.  The goal for this feature is try to help everyone find those sudden parameter sniffing queries.

SET_AX_TRACE

When you enable the long running trace functionality in Dynamics AX using SET_AX_TRACE it will remember the settings you used and automatically update new users to that setting.  There is a new processing task that will keep this updated for you.  If you enable this feature for all users, it will update any new users added to the system and enable it for them as well.

 

AOTEXPORT

The AOTEXPORT class has been significantly enhanced.  It now writes direct to the DynamicsPerf database so there is no longer a need to modify your table schema.   We now collect better Batch Job and Batch Server information then prior versions.  We also are now collecting License and Config Key information to better give guidance on features that could be disabled.  We can also show AX table sizes and activity by Config Key/Modules.  We’ve added some other configuration information that may impact the performance of your Dynamics AX system.

 

Parsing of QUERY_PLANS

Ever had problems trying to read a very large graphical execution plan?  We now parse out that XML into a table called QUERY_PLANS_PARSED.  We have broken out all of the table nodes and sorted them by estimated_rows descending.  This data is now displayed in new columns of all the QUERY views to make it easier to find those challenging performance issues on large query plans.

 

Extended Events

All of the database blocking has now been consolidated into SQL Server Extended Events.   For now, this matches our current capabilities.  We will be enhancing our data collection of Extended Events to introduce new capabilities.

 

Parameters, Parameters, Parameters

Installation of DynamicsPerf 2.0 is now driven by tables in the database vs. having to edit SQL Jobs.  There are so many options now that there is a separate script for configuring them all.  The good news is you shouldn’t have to change any of them.  The defaults will work well for everyone.

 

Many, Many new sample queries

There are a significant amount of new sample queries. It's based on all of the new data and capabilities that DynamicsPerf 2.0 gives us to analyze performance.

 

 

HOW DO I GET IT?

http://DynamicsPerf.codeplex.com

 

Rod “Hotrod” Hansen

Comments (6)
  1. Greg Y says:

    I notice several references to “Change Data Control” – is that supposed to be “Change Data Capture”?

    1. Rod Hansen says:

      yes. I’ll update it in a future release. This is not used by Dynamics AX so should be empty.

  2. How do I perform an upgrade and retain all my existing data?

    1. Rod Hansen says:

      There is an upgrades folder that contains the script to run to upgrade an existing 2.0 RC0 database to RTM. There is no upgrade from 1.x any version to 2.0.

  3. Xavier Goret says:

    Hi Rod

    First of all, congratulation for this great tool !!!

    I detected 2 problems.

    1 ) When you insert a comment using your sample, the SERVER_NAME and DATABASE_NAME is missing.
    I updated the script like that :
    INSERT INTO [COMMENTS]
    ([SERVER_NAME],[DATABASE_NAME],[QUERY_HASH],[AX_ROW_NUM],[CREATED_ON],[MODIFIED_ON],[CREATEDBY],[MODIFIEDBY],[TICKET_NUM],[COMPLETED],[COMPLETED_ON],[STATUS],[COMMENT])

    VALUES
    (‘MyServer’,
    ‘MyDatabase’,
    0x0965B450700E019E

    2 ) In query MISSING_INDEX_QUERIES, the filter should be WHERE C.QUERY_HASH = MISSING_INDEXES_CURR_VW.QUERY_PLAN_HASH and not WHERE C.QUERY_HASH = MISSING_INDEXES_CURR_VW.QUERY_HASH

    Xavier

  4. I just checked and download unavailable do you known when we can download v2?

  5. Victor Isakov (MCA, MCM, MVP) says:

    Hi Rod,

    Do you have any ETA for when it will be uploaded?

    TIA,

    Victor

Comments are closed.

Skip to main content