Query Store Hints

Microsoft just announced that Query Store Hints have made it into public preview for Azure SQL Database (thank you Joe and team).  For any of you with a single database, elastic pool, managed instance (!) or hyperscale, that means you have access to the feature today.

What do you mean, Query Store Hints?

Joe Sack and Pedro Lopes first mentioned Query Store hints in their Azure SQL: Path to an Intelligent Database session at the 2020 PASS Summit.  (You can watch the video here, thanks RedGate!)  As many of you know, the query optimizer typically generates a good plan for a query, but there are times where you might want (or need) to add a hint to the query to direct the optimizer in its decision.  Common query hints are adding OPTION (RECOMPILE) to force a recompile for every execution or OPTION (MAXDOP 1) to force a query to run single-threaded. 

Query hints can be really helpful in some scenarios, but if you do not have direct access to the code (hello third party applications), then you cannot add a query hint to the statement.  Historically, you’ve been able to use a plan guide to apply a hint, and that doesn’t require direct code access, but plan guides are not always intuitive and I find that most people are not familiar with how to use them.

Enter Query Store Hints.  With this feature, you can now add a hint to a specific statement, even if you don’t have access to the code base.  You simply need to have Query Store enabled, and for anyone using Azure SQL Database, that’s turned on by default. 

Let’s start exploring.

I’ve connected to my Azure SQL Database via Management Studio (I still prefer SSMS to Azure Data Studio because it has the Query Store reports available), and I also confirmed that Query Store is enabled.  I cleared out any old data I had in there (because this is a demo database; do not clear out your QS data in production) and ran a query:

SELECT o.CustomerID, ol.OrderID, ol.OrderLineID, o.OrderDate
FROM Sales.Orders o
JOIN Sales.OrderLines ol
	ON o.OrderID = ol.OrderID
WHERE o.CustomerID = 402
ORDER BY CustomerID, o.OrderID;
GO

I opened up the Top Resource Consuming Queries report and found my query, and checked the plan:

query_id 1 and its plan, which uses a nested loop
query_id 1 and its plan, which uses a nested loop

Ok, there’s a nested loop and seeks against the two nonclustered indexes, and honestly, this is a fine plan.  But let’s see what happens if I use Query Store to hint it to use a hash join instead.  I need the query_id for the query, which is 1 as noted in the screenshot above.  The T-SQL syntax I need to add the hint is:

EXEC sp_query_store_set_hints @query_id = 1, @value = N'OPTION (HASH JOIN)';
GO

After executing, I run the query again, then refresh the Top Resource Consuming Queries report:

query_id 1 and its new plan, after the HASH JOIN hint is applied
query_id 1 and its new plan, after the HASH JOIN hint is applied

Notice that I now have two plans for the query, plan_id 1 and plan_id 7, and the new plan uses a hash match instead of nested loop.

It’s pretty damn cool.  If you are not using Azure SQL Database do not despair, this feature will likely make it into vNext…so start thinking about your upgrade.  If you’re wondering when you might want to use Query Store hints, here are a few additional examples based on customer scenarios I’ve encountered in the last couple months:

  • You upgraded to SQL Server 2019 and you moved to compatibility mode 150, but you’re still using the Legacy CE.  You have one query which performs worse than it did prior to the upgrade, and it’s because of batch mode on rowstore. 
EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N'OPTION(USE HINT(''DISALLOW_BATCH_MODE''))';
  • MAXDOP for the instance is set to 8, but you have a handful of queries that perform better at MAXDOP 12.  You don’t want to change the setting for the instance (or at the database level) just for those queries.
EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N'OPTION(MAXDOP 12)';
  • Intermittently there are RESOURCE_SEMAPHORE waits for the instance, and you’ve discovered that there are a few big reporting queries that each take a huge memory grant.  You have identified the problem queries, but you have to work with the developers (or vendor) to get them changed and that’s going to take time.  Also, you’re running Standard Edition, so you cannot use Resource Governor to control them.  
EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N'OPTION(MAX_GRANT_PERCENT=1)';
  • You’re running in compatibility mode 150 and you need a handful of queries to use an older compatibility mode, but the same CE version. 
EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120''))';
  • You’re using the new Cardinality Estimator for a database, but you have one query that runs frequently and is critical to the application that performs better with the legacy CE.
EXEC sys.sp_query_store_set_hints @query_id= 1, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

It is difficult, if not impossible, for every query in a workload to perform optimally under one specific set of configuration options.  Further, while we can spend hours tuning and tweaking, sometimes code, schema, and configurations can’t happen quickly in a production environment.  When you need to manage performance of the variants, Query Store Hints truly fills the gap.

A few points worth mentioning:

  • Not every query hint is currently supported.  Supported hints are detailed in the  documentation, and it’s a good start.  A few that are missing that are noteworthy to me are table hints (e.g. index hints) and QUERYTRACEON (which is more relevant for on-premises than Azure SQL Database).  If these are hints you think are important, then please provide that feedback via the options listed in the original announcement post.  Remember: this feature is currently in public preview.  Do not assume that this is “it” for the feature.  There is more to come and if you want to help drive that direction, speak up now.
  • If the hint that you add cannot be used for the query, your query will not fail.  It will still go through normal compilation and optimization.  This is the same behavior we see with failed forcing, and it means you will want to monitor for failures. 
  • The new view you’ll want to check out is sys.query_store_query_hints.

I will definitely have more posts as I continue to explore this feature and help customers with production scenarios. I look forward to hearing what you all think about Query Store Hints!

8 thoughts on “Query Store Hints

  1. This is a great feature, life saver for dealing with some rogue queries after upgrade. Any idea when it will be available for SQL 2019 (non azure)? I hope it can be done via CU.

    1. Hi – I have no information on when this will be available in an on-prem release. I don’t expect that they will backport it to 2019, so my guess is that it will be in vNext, and I have no idea when that will be released.

  2. This is a great article, I love that you’ve included some use cases.
    After reading this, I immediately had a candidate in mind and applied a RECOMPILE hint, seeing a 90% reduction in duration and 30% reduction in CPU. Excellent! However after a week I revisited it and discovered to my dismay that the query_id had changed, and the hint is no longer being applied. The query text is identical (used a comparison tool and confirmed). It turns out that the query_id regularly changes for this query.
    Anyway, this is still an excellent ability to have on hand for those emergencies when you need a hint and don’t want to modify the code.

    1. Hey John-

      The query_id changes, but does the query_text_id? I would be interested to see if it’s the same query_text_id, and if it is, that means something like object_id or context_settings_id changed.

      Erin

      1. Thanks for your reply Erin, I am monitoring the query store hints I’ve implemented so far and will check up on those things you’ve mentioned next time I find a change in query_id.

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.