QueryStore practical everyday use

  • Hi

    We have been using QueryStore since day 1 of our SQLServer 2016 installation

    For every day usage though, the standard reports does not suffice to the diffent tasks/challenges that can occur, or it takes quite some time to find the culprit via the reports

    Therefore we have build a number of stored procedures using the querystore tables.

    In the process of building those i have not found many blogs/articles describing how to use Querystore, apart from the basic intro articles

    To help others get past the same barrier i am thinking of writing an article showing what we have done:

    QS_GetQueryID - returning the query_id(s) of specified object, very usefull when wanting to use the report 'tracked query'
    Optional parameters gives the possibility to:
    select timeperiod,
    specific object

    QS_GetExecutionError - returning name of object and failed execution count
    Optional parameters gives the possibility to:
    select timeperiod,
    specific object

    QS_ObjectExecutionStatistics - returning a dataset similar to the report 'Object Execution Statistics' runnable from SSMS, just using the querystore
    Optional parameters gives the possibility to:
    select timeperiod,
    reporting level: verbose og summary,
    specific object,
    and more

    QS_ChangedPerformance - Returning a dataset similar to the QueryStore report 'Regressed queries'
    Optional parameters gives the possibility to:
    select timeperiods to be used for the calculation (default is the same as the report, last hour against last week),
    selecting progression or regression of the querys,
    specify list of objects to investigate,
    and more

    Do you think it will be of interest ?

    I have started the writing process, but i am a litle uncertain to how safe the procedures are supposed to be
    I mean should i have every issue on giving wrong parameters covered or is it ok just to point out that 'it's a work in progress'

    Regards

    Peter Tilsted

  • This would be interesting. What Id' say is that you shoudl write about how you use this to help you, not try to solve every possible issue. You built these procs to solve a problem, so solve that problem and explain it.

  • ok, i'll do that

  • i just got bumped :-),
    i haven't forgotten the article
    i will be presenting the material in a local usergroup next month, and will be using there feedback when writing the article
    regards
    Peter

  • Hi Steve,
    I have done a few presentations about Query Store during this year and also started to write a series or articles here at SCC but because of the time I have not had the chance to complete the writing but I will have some free time now at the end of the year.

    Do you think this is still an interesting topic to write about? If you agree with that, I will do my best to complete the articles during this last two weeks of the year.
    Thanks!

    Carlos Robles  

    DBA Mastery
    Data Platform MVP | MCSE, MCSA, MCTS, MCP | ITIL v3

    w: www.dbamastery.com

    e: crobles@dbamastery.com

     

  • I do. It will gain adoption over time, so documenting and explaining this better is something we'd like to do.

  • Steve Jones - SSC Editor - Wednesday, December 19, 2018 1:44 PM

    I do. It will gain adoption over time, so documenting and explaining this better is something we'd like to do.

    Perfect! Then I will start working on it right away.

    Carlos Robles  

    DBA Mastery
    Data Platform MVP | MCSE, MCSA, MCTS, MCP | ITIL v3

    w: www.dbamastery.com

    e: crobles@dbamastery.com

     

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

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