March 31, 2020 at 11:55 am
The only two runtime data sets that we poll and save periodically is the below querying the Query Store.
Would you suggest to save more columns and or possibly a better TopQueries collector as this? Is there a better way of saving this kind of performance metrics? Should We be saving more than just 25 in a batch? (I inherited this repository from DBAs and now need to make sense out of it and stop unnecessary data collector jobs and possibly introduce new collector routines).
insert into DBA_Utilities.dbo.TOPqueries
select top 25 getdate(), t.query_sql_text,r.last_logical_io_reads,
r.avg_logical_io_reads,r.count_executions,r.avg_duration
from sys.query_store_query as q
join sys.query_store_query_text as t
on t.query_text_id=q.query_text_id
join sys.query_store_plan as p
on p.query_id=q.query_id
join sys.query_store_runtime_stats as r
on r.plan_id=p.plan_id
where count_executions>=5
and r.avg_duration>='10000000'
order by r.avg_duration desc
Likes to play Chess
March 31, 2020 at 12:26 pm
The saved results look like in the attached screenshot.
My other doubt is whether it is worth at all to save/persist this info for baselining or other purposes.
Should the results of sp_WhoIsActive, for example, be sufficient enough ?
I am thinking about what possible production problem could be investigated by using this saved data, in what ways.
Likes to play Chess
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply