Query Store Questions: 24HOP follow up

 

A couple weeks ago I presented for the 24HOP Summit Preview and I had a lot of great general questions about how Query Store works.  My session title was “Why You Need Query Store” (you can watch it here) and I only had about 45 minutes.  As you can probably guess – since I have a full day pre-con on the topic – I can talk about Query Store for a loooong time 🙂  The 24HOP session was really focused on getting folks to understand THE most important things that Query Store provides to show why it’s needed.  I left out a lot of details about HOW Query Store works because talking through it is the fun stuff that I’ll dive into during the pre-con.  I did have a good number of questions from attendees related to specific functionality, and I promised to write a post answering them.  Questions* and answers are below…if you need clarification on anything, please leave a comment and I’ll follow up!

*Questions copied exactly as they were shared with me, I did not try to re-word or make any inferences about what was being asked.

Questions

1. Can I get Query Store data for a production database deployed on a client site, that I don’t actually have access to myself? Can the DBA send me something I can use in my own development environment?

A: You provide instructions to the client that explain how to enable Query Store, either through the UI or with T-SQL.  If you want to view that Query Store data, the client can either send you a backup of the database or create a clone with DBCC CLONEDATABASE and share that.

 

2. If a user executes a stored procedure from ‘master’ it is not captured?

A: If you have Query Store enabled for a user database, and you execute a query against that user database from the context of the master database, it is not supposed to be captured in Query Store for that user database.  But in my testing, it is.  But it is not supposed to be, so there is no guarantee it will always work that way.

 

3. If your database is part of an AG the data you look at can be different based on the server it is running on at that time, correct?

A: I’m not quite clear what’s being asked, but I wrote a post about Query Store and Availability Groups, which will hopefully answer the question.

 

4. Is it easy to remove the forcing of a given plan?

A: Yes, just use the “Unforce Plan” button in the UI, or use the stored procedure sp_query_store_unforce_plan (you supply the query_id and plan_id).

 

5. If you have 3+ plans how does SQL Server decide which plan to use?

A: I assume this is specific to the Automatic Plan Correction feature, and if so, it will force the last good plan (most recent plan that performed better than the current plan).  More details in my Automatic Plan Correction in SQL Server post.

 

6. What equivalent options we have for lower versions?

A:  There is an open-source tool called Open Query Store for versions prior to SQL Server 2016.

 

7. Why are the trace flags not on by default? given the issues with AlwaysOn and QS

A: Great question.  Trace flag 7752 will be default functionality in SQL Server 2019.  TF 7745 is not default functionality because, I suspect, of the potential for losing Query Store data…and SQL Server wants you to make a conscious choice about that.  More details in Query Store Trace Flags.

 

8. How would you use Query Store to troubleshoot Views?

A: Query Store does not differentiate between a query that references a view and a query that references a table.  It does not capture the object_id of the view and store that in Query Store (as happens for a stored procedure), so you have to look specifically for the view name in the query_sql_text column (within sys.query_store_query_text) to look for queries that reference the view.

 

9. Is there any way to make use of Query Store in readonly secondary AG replicas?

A: You can read data from the Query Store views on a read-only replica, but you cannot capture data in Query Store about queries executing against the read-only replica.  See my post referenced in #3, and then please up-vote this request: Enable Query Store for collection on a read-only replica in an Availability Group.

 

10. Is it possible that a query store run from one instance to another instance for example I want check the queries of production from dev instance?

A: If you can connect to the production instance from the dev instance, and have appropriate permissions, then you can query the Query Store data on the production instance (but the data exist in the production database).

 

11. If I execute a parameterized query with OPTION (RECOMPILE), will Query Store have the parameter values of every execution?

A: No.  The plan will have the values used for the initial execution that generated said plan, but values for every individual execution are not captured (it would generate excessive overhead to capture every execution).

 

12. Can Query Store supply the T-SQL to force plan?

A: The UI does not provide an option to script forcing a plan for a query, but if you are using Automatic Plan Correction, the T-SQL to force it can be found in sys.dm_db_tuning_recommendations.

 

13. Will there be any significant performance overhead by using query store?

A: See Query Store Performance Overhead: What you need to know

 

14. How does it function when queries span multiple databases?

A: As alluded to in question #2, cross-database queries are tricky.  You should work under the assumption that if you execute a query from Database_A, where Database_A has Query Store enabled, it will be captured.  If you execute a query from Database_A that queries both Database_A and Database_B, and both databases have Query Store enabled, it will ONLY be captured in Database_A.

 

15. It seems to be working for me, but sometimes not

A: I would love to help you out, just not sure of the behavior you’re seeing and what your question is.

 

16. How do you get the full query text from inside the ‘Top Resource Consuming Queries’ windows?

A: Click on the button with the grid and magnifying glass, which says “View the query text of the selected query in a query editor window” when you hover over it.

Button to display query text to help understand how query store works

Button to “View the query text of the selected query in a query editor window”

 

17. Is the data will be stored in Query Store After the adhoc/SP completed or it will do while is running?

A: Once the plan has been compiled for a query, the query text and plan are sent to Query Store.  When execution completes, the runtime statistics are sent to Query Store.

 

18. If we change the compatibility to SQL 2012 or lower, will that affect Query store?

A: No, Query Store functions in SQL Server 2016 and higher, and Azure SQL Database, regardless of your compatibility mode.

 

19. If we drop a SP, will that clear the history of that SP plans in the query store?

A: No, but…If you use DROP PROCEDURE syntax, then the object_id column in sys.query_store_query will no longer reference an existing object (in sys.objects).  The query and plans will stay in Query Store until they are aged out based on the retention policy.

 

Follow Up

Again, if any answers are unclear, leave a comment and I can clarify.  If you are interested in learning more about Query Store I would love to see you in my full day session at the PASS Summit!  It’s on Monday, November 5th, and you get more details here: Performance Tuning with Query Store in SQL Server

Why You Need Query Store, Part III: Proactively analyze your workload

 

The amount of data collected by Query Store pales in comparison to the amount of data available in all of SQL Server that could be captured.  But the quality of the data overshadows the quantity, or lack thereof.  While most people target the execution statistics when viewing historical data in Query Store, I would argue that the query text and query plan information are equally as valuable when it comes to taking the time to analyze your workload.

The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively.  The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max).  This means that the data can be mined, looking for patterns and explicit use of objects.  Want to know what queries use an index?  Look for it in the plans.  Want to know what queries have a RECOMPILE hint on them?  Look for it in the query text.  Anything you want to find with regard to query text or plans is there, you just have to know how to query the data.

Mining Query Store: Queries

But wait, can’t we just search the text?  Sure, with the query_text field you can just wildcard your way through it looking for things.  For example, the query below will help you find every statement in Query Store that has RECOMPILE in the text.

SELECT
     [qsq].[query_id],
     [qsp].[plan_id],
     [rs].[last_execution_time],
     [rs].[avg_duration],
     [rs].[avg_logical_io_reads],
     [qst].[query_sql_text],
     TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
     ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
     ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
     ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qst].[query_sql_text] LIKE '%RECOMPILE%';
GO

Mining Query Store: Plans

Query plans have a specific format, defined by Microsoft, which can be searched using wildcards, but the performance is abysmal and you’re better served using XQuery.  That’s right, I said XQuery.  If you don’t know, XQuery and I are frenemies.  I love the information I get when using it, but writing the T-SQL always makes me want to compulsively eat M&Ms and animal crackers until I feel sick.

Fortunately for me, Jonathan writes XQuery and creates the foundation of the queries I manage to cobble together for Query Store.  In his post, Finding what queries in the plan cache use a specific index, Jonathan has the code to look through the plan cache and find what queries use a specific index.  The challenge is that the plan cache can be volatile, particularly in an ad hoc workload.  Even in a parameterized/procedural workload, there is churn and the plans that are cache change as different business processes run throughout the day, week, and month.

Consider the scenario where you use sys.dm_db_index_usage_stats to determine if an index is being used, and when you find one that it isn’t….how do you really know?  As we know, the plan cache is transitory, so a query that uses it might not be in cache at that moment.  But it would be in Query Store, depending on your retention settings.  If we modify the query from Jonathan’s post, we can interrogate the Query Store data:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

DECLARE @IndexName AS NVARCHAR(128) = '[FK_Sales_InvoiceLines_InvoiceID]';
-- Make sure the name passed is appropriately quoted

IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName);
--Handle the case where the left or right was quoted manually but not the opposite side
IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName;
IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']';
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
     stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
     obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
     obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
     obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
     obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
     obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
     query_plan
FROM
(
     SELECT query_plan
     FROM
     (
          SELECT TRY_CONVERT(XML, [qsp].[query_plan]) AS [query_plan]
          FROM sys.query_store_plan [qsp]) tp
          ) AS tab (query_plan)
     CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
     CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);

Summary

Beyond looking at performance metrics over time for a query, take advantage of the plethora of data in the Query Store tables.  Use both the text and plan information to proactively look for anti-patterns in your workload: use of index hints, RECOMPILE, NO LOCK…all the things that may cause problems, and understand both object access and expensive operations in the plans.  There is no limit to the patterns, and anti-patterns, you can find.  You just have to write the code.

Why You Need Query Store, Part II: Plan forcing

We hear a lot about all the data that Query Store captures, but it is tempting to argue that the best part of Query Store is the ability to force a specific plan for a query.  Arguably, if the data didn’t exist, we would have a harder time figuring out what plan to force, but plan forcing with Query Store is light-years easier than using a plan guide (the historical method for getting a query to use a specific plan).  Further, tracking what plans are forced and how the queries are performing is easy too, using the Queries With Forced Plans report in SSMS.

Items to Consider

Before you go too crazy trying to stabilize performance with hundreds of forced plans, here are a couple things worth mentioning about plan forcing.

  1. I think of plan forcing as a temporary solution. I do not view it as a “set it and forget it” option.
  2. Use T-SQL for forcing and un-forcing (versus the UI) so you can track when it was done, and by whom, in change control. Nothing is entered in the ERRORLOG, or anywhere else, and there is currently no event that captures it.
  3. Evaluate both the execution statistics (average and total) and the different plans for the query to determine the “best” plan to force.

Why do I view plan forcing as temporary?  Because if I have plan variability, I’d really like to address it through code or schema changes.  Maybe there’s an index to add.  Maybe a section of the query needs a re-write.  To me, forcing the plan gives me a way to “stop the bleeding” – I can stabilize performance right now – until I can determine the right long-term solution and implement it.

For those of you with third-party applications, who cannot change code or indexes, you still want to monitor performance of those forced plans.  While you can’t change the code or the schema, the vendor can (and most likely will at some point), and your data will change.  Changes in data and its distribution could have a greater impact on plan choice than schema and code, and you may be preventing a better plan from being used by forcing the one that was good six months ago.

Deciding What Plan to Force

When you are looking at plan performance, deciding which plan to force can get tricky, depending on how many plans you have for a query.  I start by looking at the plans – does one of them stand out as a “better” plan because of the operators it uses (or doesn’t use), the lack of warnings in the plan, etc.  Then I look at the execution statistics, and this is where you want to make sure you know what metric is displayed in the UI.  Examine graph A below, and decide what plan you would force before you continue reading.

Total plan duration over time

Total plan duration over time

Graph A shows plan performance for query_id 137512 across a 48-hour window.  They y-axis is duration, and notice that it’s TOTAL duration.

Now take a look at graph B, below, which shows plan performance for the same query_id across the exact same window of time, but the y-axis is AVERAGE duration.  Does your forcing decision change?

Average plan duration over time

Average plan duration over time

When we look at average duration across the different plans, it’s quite consistent.  So much so that I probably wouldn’t force a plan for this query.  I may investigate the plans in more detail to understand what the variation is, but for the most part, I’d probably leave it.  Remember that total duration represents the total amount of time spent executing the query during a time interval.  So the plans that had a lower total duration most likely were not executed as many times as those with a higher total duration.  This becomes evident when you hover over one of the circles and look at the metrics for that specific data point, as shown below.

Query performance details for a highlighted plan

Query performance details for a highlighted plan

Summary

I think plan forcing is a fantastic feature in SQL Server, whether you’re manually forcing plans, or using Automatic Plan Correction.  If you are forcing plans, take the time to decide the “best” plan to force by not just reviewing the plan and the execution statistics, but also by testing the plan with different input parameters.  This can really to understand what plan consistently provides stable performance for a query.  I recommend setting up an Extended Events session to monitor for forcing failures, and regularly re-visit what plans are forced to see if they are still appropriate.  Finally, remember that if you turn off Query Store for any reason, plan forcing is not longer in effect.  But if Query Store happens to flip to READ_ONLY, as long it’s enabled, your plans will still be forced.