Another one of the new features in version 2.0 is the addition of HISTORICAL TABLES:
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,
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.
I hope you’ve enjoyed this insight into one of the new features in DynamicsPerf 2.0.
Rod “Hotrod” Hansen