I decided to resume tech blogging after a long break and this tsql-tuesday came in handy. This month’s blog part is hosted by John McCormack (B|T). He would like us to blog about handy scripts.
I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.
My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.
SELECT q.object_id,object_name(q.object_id),q.query_id,max_duration, avg_duration, max_rowcount,
p.plan_id,i.start_time,i.end_time
FROM sys.query_store_runtime_stats AS a
JOIN sys.query_store_runtime_stats_interval i
ON I.runtime_stats_interval_id = a.runtime_stats_interval_id
JOIN sys.query_store_plan p on p.plan_id = a.plan_id
JOIN sys.query_store_query q on p.query_id = q.query_id
WHERE q.object_id = object_id(‘dbo.myproc’)
order by i.start_time DESC
My next favorite one is one I use to find a plan based on text in the query.
SELECT c.plan_id, cast(c.query_plan as xml) , c.last_execution_time
FROM sys.query_store_plan C INNER JOIN sys.query_store_query B
ON C.query_id = b.query_id
INNER JOIN sys.query_store_query_text A ON
B.query_text_id = A.query_text_id
WHERE A.query_sql_text like ‘tablea’
The last one is duration of specific queries over time.
SELECT TOP 100 avg_duration/1000000.0 avg_dur_sec
FROM
sys.query_store_runtime_stats WHERE plan_id = 4962438
order by runtime_stats_id DESC
If you are reading this and not using query store yet – you must. Consider signing up for Erin Stellato’s precon too at the upcoming past community summit. It may be a good use of your time and money.