page contents

DynamicsPerf 2.0 Historical Tables and Baselines


Another one of the new features in version 2.0 is the addition of HISTORICAL TABLES:

  • QUERY_HISTORY
  • INDEX_HISTORY

We collect query data every 5 minutes by default and index data every hour on the hour. With this increase in collection of detailed data, it was pretty apparent that the DynamicsPerf database would quickly grow to large.We needed to collect the detail more frequently so that we can look at smaller timeframes of activity and be able to really drilldown on performance issues. The scenario where we needed more detail was when somebody walks in the office and says “Hey, Dynamics is slow”. In that situation you need lots of detailed information. With this new collection, you no longer have to remember to run the capturestats SQL Job to collect data; it’s already been done for you.

The challenge then is how do we store that detailed data over time so that you can do longer term performance analysis. This is where the idea for these 2 new tables comes into the solution. The collection task is collecting the data every 5 minutes and the processing task is aggregating that data into the history tables every 5 minutes as well but 1 minute later. If you look at the 2 SQL Agent jobs you will see they are offset by 1 minute.

With this design, we can keep the aggressive detailed data but just purge it more often. So, in this version, detailed data is kept for 2 days.The history tables actually have 2 record types: D for Daily and M for monthly.
The aggregation process updates both of these sets of data. There is a bit of magic in this process, the aggregation process actually computes the delta values from the detailed tables.

Let me explain how this works and how the DMV data is kept in SQL Server. Let’s assume a query is used a lot and stays in procedure cache.  This is what the data would look like in QUERY_STATS:

Date      Time      Query_hash          execution_count         execution_time

1/1/2016  12pm   0x1233445            100                              23000

1/1/2016 4pm     0x1233445             150                              32000

1/2/2016  12pm   0x1233445            500                              456000

In this data, the 2nd data collection is on the same day.  If the aggregation code did a simple sum of all the records on 1/1/2016 it would suggest that 250 executions of the query occurred.  That would be inaccurate because the DMVs keep an aggregated total for the query as long as the query is in procedure cache.   Our processing tasks, actually know how SQL is managing this and figures out the deltas.  It then adds the deltas into the D daily record and the M monthly record.  In this case, it would show 150 executions for the query on 1/1/2016.

That’s probably a bit more detail about the inner workings then most probably need to know.  What this feature really does for everyone is make it easy to analyze data across time like the baseline queries we had in prior versions.  The difference is that you don’t have to do a baseline capture to do that work any more.   With this new schema, we have the ability to compare a query to how it was running on any day to any other day.  We can also compare how it was running today to how it’s been running this month. You can also compare it to how it was running last month.  Here is that example query:

— Show all queries who’s avg is greater then previous month

        SELECT TOP 100 QM.AVG_ELAPSED_TIME,
                       QS.*
        FROM   QUERY_HISTORY_VW QS — Queries from last data collection only
               INNER JOIN QUERY_HISTORY_VW QM
                       ON QS.QUERY_HASH = QM.QUERY_HASH
                          AND QS.QUERY_PLAN_HASH = QM.QUERY_PLAN_HASH
                          AND QM.DATE = Dateadd(MONTH, Datediff(MONTH, -1, QS.DATE), 0)
                          AND QM.FLAG = ‘M’
                          AND QS.AVG_ELAPSED_TIME > QM.AVG_ELAPSED_TIME
        WHERE  QS.FLAG = ‘M’ –AND QS.DATE = ‘9/1/2015’
        ORDER  BY QS.TOTAL_ELAPSED_TIME DESC

Another really good side benefit of this design is that the DynamicsPerf database actually has been staying smaller than in previous versions.  This is despite collecting more detailed data.  It’s the aggregation that saves us space and allows us to do long term performance analysis.

History Tables

I hope you’ve enjoyed this insight into one of the new features in DynamicsPerf 2.0.

 

Rod “Hotrod” Hansen

Skip to main content