Saving Top Queries from Query Store for historical performance analysis?

  • VoldemarG

    Hall of Fame

    Points: 3640

    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,


    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

    Voldemar likes to play CHESS (and IS good at it!)

  • VoldemarG

    Hall of Fame

    Points: 3640

    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.

    You must be logged in to view attached files.

    Voldemar likes to play CHESS (and IS good at it!)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply