page contents

DynamicsPerf 2.0 Scheduling Engine


 

One of the new features of DynamicsPerf 2.0 is the way tasks are now run using a scheduling engine inside of DynamicsPerf. There were 2 goals for this new design.  The first being able to deliver customized data collection for different environments.  A good example of this would be for collection at a retail store where we might only want to collect Query_Stats information only.  The 2nd goal of this design was to simplify the setup of DynamicsPerf.  In DynamicsPerf 2.0, every data collection piece is now it’s own stored procedure.  We could have added a SQL Agent Job for each collector, but as you can imagine, adding 50+ SQL Jobs to your system wouldn’t have been very manageable.  Thus, we built the system we did.

The engine is made up of 3 tables and 2 stored procedures that drive the new way we collect data with DynamicsPerf 2.0. The tables are:

  • DATABASES_2_COLLECT
  • DYNPERF_TASK_SCHEDULER
  • DYNPERF_TASK_HISTORY

 

DATABASES_2_COLLECT is the table that contains 1 record for each database to be collected.  This also contains some of the defaults for data retention.  This table gets populated as part of the installation process. 

DYNPERF_TASK_SCHEDULER is the table that contains 1 record for each collector stored procedure.   These are all of the collectors with their default schedule for collection. 

DYNPERF_TASK_HISTORY is the table that keeps 1 record per task per database.  This also has additional fields so that you can override the default schedules from the other tables to tailor the data collection per task/database combination.

 

There 2 stored procedures that drive the process are :

  • SP_CAPTURESTATS
  • SP_PROCESSSTATS

 

SP_CAPTURESTATS is the stored procedure responsible for all of the COLLECT type tasks in the DYNPERF_TASK_SCHEDULER table. 

SP_PROCESSSTATS  is the stored procedure responsible for all of the PROCESS type tasks in the DYNPERF_TASK_SCHEDULER table.

 

This design allows us to breakup some of the work from the old design into simultaneous tasks.  An example of one of the items that has changed in this design is how we handle the QUERY_PLANS table.  In prior versions of DynamicsPerf, when we collected the QUERY_PLANS data we parsed out the query parameters when we inserted the records.  With the new design, we can delay this parsing step until later, which improves the performance of collecting the data.  It also allows us to do more detailed parsing of the QUERY_PLANS providing much more information which you will find in all of the QUERY type views in DynamicsPerf analyze solution.

 

NOTE: One side effect of this design is that if you look at Query data and there are no query parameters shown,  it means that the process task to parse the QUERY_PLANS table has just not run yet for the data that you are reviewing.  Just wait a few minutes and the parsing task will run and the data will be there.  If you run SP_CAPTURESTATS manually passing parameters, it will ignore the schedule and collect all data.

 

Here is the list of COLLECT tasks and schedules:

TASK_PROCEDURE

SCHEDULE_UNITS

SCHEDULE_QTY_PER_UNIT

DYNPERF_COLLECT_QUERY_STATS

MI

5

DYNPERF_COLLECT_INDEXSTATS

HH

1

DYNPERF_COLLECT_SQL_TEXT

MI

5

DYNPERF_COLLECT_QUERY_PLANS

MI

5

DYNPERF_COLLECT_SYSOBJECTS

DD

1

DYNPERF_COLLECT_WAITSTATS

HH

1

DYNPERF_COLLECT_VIRTIALIO_DISKSTATS

HH

1

DYNPERF_COLLECT_CHANGE_DATA_CONTROL

DD

1

DYNPERF_COLLECT_CHANGE_TRACKING

DD

1

DYNPERF_COLLECT_SSRS_EXECUTIONLOG

MI

5

DYNPERF_COLLECT_SQL_DATA_BUFFER_CACHE

DD

1

DYNPERF_COLLECT_SQL_DATABASES

DD

1

DYNPERF_COLLECT_DATABASE_REPLICATION_INFO

DD

1

DYNPERF_COLLECT_SQL_CONFIGURATION

DD

1

DYNPERF_COLLECT_SQL_DATABASE_FILES

DD

1

DYNPERF_COLLECT_DATABASE_VLFS

DD

1

DYNPERF_COLLECT_INDEX_USAGE_STATS

HH

1

DYNPERF_COLLECT_INDEX_OPERATIONAL_STATS

HH

1

DYNPERF_COLLECT_SQL_JOBS

DD

1

DYNPERF_COLLECT_SERVERINFO

DD

1

DYNPERF_COLLECT_SERVER_REGISTRY

DD

1

DYNPERF_COLLECT_SERVER_DISKVOLUMES

WK

1

DYNPERF_COLLECT_SERVER_OS_INFO

WK

1

DYNPERF_COLLECT_TRIGGER_INFO

DD

1

DYNPERF_COLLECT_SQL_TRACEFLAGS_RUNNING

DD

1

DYNPERF_COLLECT_SQL_ERRORLOG

MI

5

DYNPERF_COLLECT_DATABASE_STATISTICS

WK

1

DYNPERF_COLLECT_SQL_PLAN_GUIDES

DD

1

DYNPERF_COLLECT_PERF_COUNTERS

MI

5

DYNPERF_COLLECT_PERF_COUNTERS_AZURE

MI

5

DYNPERF_COLLECT_AX_SQLTRACE

MI

5

DYNPERF_COLLECT_AX_SQLSTORAGE

WK

1

DYNPERF_COLLECT_AX_NUMBERSEQUENCE

HH

1

DYNPERF_COLLECT_AX_SYSGLOBALCONFIG

DD

1

DYNPERF_SET_AX_SQLTRACE

DD

1

DYNPERF_COLLECT_CRM_ORGANIZATION

DD

1

DYNPERF_COLLECT_CRM_PLUGINS

DD

1

DYNPERF_COLLECT_CRM_POA_TOTALS

HH

1

MI = Minutes

HH = Hours

DD = Days

WK = Weeks

MM = Months

QQ = Quarter

YY = Year

Here is the list of PROCESS tasks and schedules:

TASK_PROCEDURE

SCHEDULE_UNITS

SCHEDULE_QTY_PER_UNIT

DYNPERF_REFRESH_QUERY_PLANS

DD

1

DYNPERF_REFRESH_QUERY_TEXT

DD

1

DYNPERF_PROCESS_AXSQLTRACE

MI

5

DYNPERF_UPDATE_QUERY_HISTORY

MI

10

DYNPERF_UPDATE_INDEX_HISTORY

HH

1

DYNPERF_PURGE_DATA

DD

1

DYNPERF_PROCESS_QUERY_PLANS

MI

5

DYNPERF_UPDATE_SSRS_HISTORY

MI

5

DYNPERF_PROCESS_QUERY_ALERTS

MI

5

 

It should not be necessary to change the defaults. 

 

The end results of the new design is that instead of having to manage multiple SQL Agent jobs to run tasks, it can all be managed via tables in the DynamicsPerf database. This also makes it very easy to add new features without having to modify existing code. 

 

Rod “Hotrod” Hansen

Comments (6)
  1. Rod Hansen says:

    Thanks Artis !!

    I'm going to put up a feedback blog for everyone.  I would love to hear what version of Dynamics you have installed Dynperf 2.0 on and your feedback on it.

  2. Atis Dimants says:

    Nice to see this tool being maintained and updated. It was good already before, now it should be great!

    BTW – I'm a bit suprised not to see this information in Dynamics AX Performance blog – blogs.msdn.com/…/axperf . Don't you, guys, cooperate?

  3. Rod Hansen says:

    All,

    Thanks for you patience.  The beta should be released in the next day or so.  I'm glad to see all of the excitement.  .  

    Rod "Hotrod" Hansen

  4. Patrick Dolan says:

    I was told it was still in Beta and hopefully out by end of this quarter.

  5. Inayathulla says:

    Can you provide the download link for DynamicsPerf 2.0

  6. Kris Cook says:

    Very disappointing that the site still only provides version 1.20.  We'd like to get started with this.

Comments are closed.

Skip to main content