In early January Microsoft announced that Connect, the method for filing SQL Server bugs and feature requests, was being retired. It was replaced by User Voice, and any bugs/requests were ported over. Sadly, the votes from Connect did not come across to User Voice, so I went through and found all the Query Store requests, which are listed below. If you could please take the time to up-vote them, that would be fantastic. If you could also take time to write about why this would help your business, help you upgrade, or purchase more SQL Server licenses, that is even better. It helps the product team immensely to understand how this feature/fix/functionality helps you and your company, so taking 5 minutes to write about that is important.
Two things…I’ve listed these in order of priority to me, but of course your priorities may be different! 🙂 Second, if I have missed a bug/request, please feel free to email me or comment so I can add it to this list. Thanks!
- Enable Query Store for collection on a read-only replica in an Availability Group
- Indicate “morally equivalent” forced plan where use_plan = true but is_forced = 0
- Kendra Little wrote a post explaining this behavior: Forced Plan Confusion: Is_Forced vs. Use Plan = True
- Query store create database
- Check out Andy Mallon’s blog post about discovering this issue: Deadlock when creating a database – A Query Store bug
- Option to store query store data in a filegroup other than PRIMARY
- Add or link views’ execution plan to a query execution plan
- SQL Server Management Studio 17.3 (14.0.17199.0) returns incorrect syntax on WAIT_STATS_CAPTURE_MODE
- QDS_LOADDB Wait type blocks all queries
- You can use trace flag 7752 to get around this, see Query Store Trace Flags
- Query Store SELECT Performance
- “Edit Query Text” query text doesn’t match the actual query in 2017 RTM
- Add an Extended Events action for query_id
- Max Plan per query missing from Query Store properties in SSMS 2016
- Still an issue in SSMS 17.5
- [SQL Server 2016 CTP 2.4 – Query Store] Functionality to collect the information of queries which are not finished
- Query Store – Add Actual memory Grant Used From sys.dm_exec_query_stats
- In SQL Server 2017, sys.query_store_runtime_stats the data captured includes:
- avg_query_max_used_memory
- last_query_max_used_memory
- min_query_max_used_memory
- max_query_max_used_memory
- stdev_query_max_used_memory
This data represents memory used for the query, not the memory grant, but if I were interested in using Resource Governor to create pools with memory limits for queries, this is pretty good information from which to work.
- In SQL Server 2017, sys.query_store_runtime_stats the data captured includes:
- Bugbash: Enable Query Store for tempdb
- Add Query Store to Model for Auto Configuration on New Databases
- As far I know, this works. If you enable Query Store for model, it will be enabled by default for new databases you create (unless you otherwise specify), see SQL Server Query Store: Default Settings.
5 thoughts on “Query Store Requests”
Great list, thanks. I’m upvoting and sharing.
Erin, thank you for taking the time to compile this list. I up-voted my top suggestions. Alex, thanks for sharing.
Hi Erin,
We enabled QueryStore to our biggest OLTP database and would like to monitor the performance of some stored procs as part of doing some conversions and upgrades. what is the best way to get this info from query store? Thanks
Hello-
You’ll need to query the Query Store views directly, as the UI reports don’t provide the option of searching by Stored Procedure.
You can start with sys.query_store_query, it has object_id in it, so for example, the query below will show you all the queries for a specific stored procedure:
SELECT
q.query_text_id,
q.query_id,
CASE
WHEN q.object_id = 0 THEN N’Ad-hoc’
ELSE OBJECT_NAME(q.object_id)
END AS [ObjectName],
qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
WHERE object_id = OBJECT_ID(N’YourSPNameHere’)
To get plan information you have to join to query_store_plan, and to get execution statistics for the different time intervals, join to query_store_runtime_stats
Hope that helps!
Thanks Erin. Below is the query I came up with. Does this look good to you? Basically, I need to monitor the execution time of a sp every 5 mins to corelate with another monitoring tool that captures IO, CPU, etc. Thanks for the help
SELECT TOP 1000000 CASE
WHEN q.object_id = 0 THEN N’Ad-hoc’
ELSE Object_name(q.object_id)
END AS [ObjectName],
–Cast(start_time AS DATE) AS Date,
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), rti.start_time) as localstarttime,
–DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), rti.end_time) as localendtime,
Sum(avg_duration / 1000000.00) AS elapsed_time_in_Sec,
— Avg(avg_duration / 1000000.00) AS Avg_Duration_in_Sec,
Sum(rts.count_executions) AS ExecutionCount
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rts
ON p.plan_id = rts.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rti
ON rts.runtime_stats_interval_id = rti.runtime_stats_interval_id
WHERE object_id = OBJECT_ID(N’XXXXX_OBjectName’)
–WHERE rti.start_time BETWEEN Dateadd(dd, -15, Cast(Getdate() AS DATE)) AND
— Dateadd(dd, -1, Cast(
— Getdate() AS DATE))
–where rti.start_time > getdate() – (60/1440)
–and rti.runtime_stats_interval_id = 1885
AND rts.execution_type = 0
–and rts.count_executions > 100000
–order by q.object_id, q.query_id, p.plan_id, rts.runtime_stats_id
–order by rts.count_executions desc, q.object_id, q.query_id, p.plan_id, rts.runtime_stats_id
— order by rts.avg_duration desc, q.object_id, q.query_id, p.plan_id, rts.runtime_stats_id
GROUP BY CASE
WHEN q.object_id = 0 THEN N’Ad-hoc’
ELSE Object_name(q.object_id)
END,
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), rti.start_time)
— DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), rti.end_time)
— p.plan_id,
–q.query_id
–HAVING Sum(rts.count_executions) >0
ORDER BY
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), rti.start_time) desc
–Cast(start_time AS DATE) DESC,
–Sum(rts.count_executions) DESC