QueryStore practical everyday use


By pti-704936 - Thursday, February 22, 2018 8:06 PM


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'


Peter Tilsted
By Steve Jones - SSC Editor - Wednesday, March 21, 2018 9:26 AM

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.
By pti-704936 - Thursday, March 22, 2018 6:25 AM

ok, i'll do that