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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy