Query Store and What Happened Earlier on Your Server
See how you can use SQL Server's Query Store's aggregations (and some forethought and planning) to help to help narrow down suspects when things go wrong.
Join the DZone community and get the full member experience.Join For Free
Here’s a great question I received: "We had a problem at 9:02 AM this morning, but we’re not sure what happened. Can Query Store tell us?"
My first blush response is, no. Not really.
Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can’t tell you what happened with an individual call at 9:02 AM…
Well, not entirely true.
The aggregations that Query Store keeps are actually broken up into intervals that you can control. The default interval is 60 minutes. This means that the information stored in the DMV covers sets of intervals. This means that if, at 9:02AM, you had a query, or queries, that ran considerably longer than normal, you may be able to take a look at them. I’m not guaranteeing this will always find what you want, but at least it’s a starting point. Here’s how we could compare a known, well behaved, period of time, to a known, ill-behaved, period of time:
DECLARE @CompareTime DATETIME, @BaseTime DATETIME; SET @BaseTime = '2016-09-22 13:33'; SET @CompareTime = '2016-09-22 12:15'; WITH CoreQuery AS (SELECT qsp.query_id, qsqt.query_sql_text, qsp.query_plan, qsrs.execution_type_desc, qsrs.count_executions, qsrs.avg_duration, qsrs.max_duration, qsrs.stdev_duration, qsrsi.start_time, qsrsi.end_time FROM sys.query_store_runtime_stats AS qsrs JOIN sys.query_store_runtime_stats_interval AS qsrsi ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id JOIN sys.query_store_plan AS qsp ON qsp.plan_id = qsrs.plan_id JOIN sys.query_store_query AS qsq ON qsq.query_id = qsp.query_id JOIN sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id ), BaseData AS (SELECT * FROM CoreQuery AS cq WHERE cq.start_time < @BaseTime AND cq.end_time > @BaseTime ), CompareData AS (SELECT * FROM CoreQuery AS cq WHERE cq.start_time < @CompareTime AND cq.end_time > @CompareTime ) SELECT bd.query_sql_text, bd.query_plan, bd.avg_duration AS BaseAverage, bd.stdev_duration AS BaseStDev, cd.avg_duration AS CompareAvg, cd.stdev_duration AS CompareStDev, cd.count_executions AS CompareExecCount FROM BaseData AS bd JOIN CompareData AS cd ON bd.query_id = cd.query_id WHERE cd.max_duration > bd.max_duration;
In this instance, I’m finding anywhere between the two intervals where the max on any of the queries that match between the intervals ran longer. We could look just at the average. We even could simply look to see if the aggregated standard deviation on duration is higher between intervals (or CPU, or I/O, the list goes on).
As I said, there’s no guarantee that we’re going to capture anything interesting, but if you don’t have any other monitoring in place that will capture query metrics at a more granular level, Query Store does offer a partial solution.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.