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,

    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

    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.

    Attachments:
    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