Query Store Requests

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!

  1. Enable Query Store for collection on a read-only replica in an Availability Group
  2. Indicate “morally equivalent” forced plan where use_plan = true but is_forced = 0
    1. Kendra Little wrote a post explaining this behavior: Forced Plan Confusion: Is_Forced vs. Use Plan = True
  3. Query store create database
    1. Check out Andy Mallon’s blog post about discovering this issue: Deadlock when creating a database – A Query Store bug
  4. Option to store query store data in a filegroup other than PRIMARY
  5. Add or link views’ execution plan to a query execution plan
  6. SQL Server Management Studio 17.3 (14.0.17199.0) returns incorrect syntax on WAIT_STATS_CAPTURE_MODE
  7. QDS_LOADDB Wait type blocks all queries
    1. You can use trace flag 7752 to get around this, see Query Store Trace Flags
  8. Query Store SELECT Performance
  9. “Edit Query Text” query text doesn’t match the actual query in 2017 RTM
  10. Add an Extended Events action for query_id
  11. Max Plan per query missing from Query Store properties in SSMS 2016
    1. Still an issue in SSMS 17.5
  12. [SQL Server 2016 CTP 2.4 – Query Store] Functionality to collect the information of queries which are not finished
  13. Query Store – Add Actual memory Grant Used From sys.dm_exec_query_stats
    1. In SQL Server 2017, sys.query_store_runtime_stats the data captured includes:
      1. avg_query_max_used_memory
      2. last_query_max_used_memory
      3. min_query_max_used_memory
      4. max_query_max_used_memory
      5. 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.

  14. Bugbash: Enable Query Store for tempdb
  15. Add Query Store to Model for Auto Configuration on New Databases
    1. 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

  1. 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

    1. 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!

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.