Blog Post

Azure SQL Blog
3 MIN READ

Query Store Hints Preview

Joe Sack's avatar
Joe Sack
Icon for Microsoft rankMicrosoft
Jun 09, 2021

Today we are announcing the public preview of the Query Store hints feature which provides an easy-to-use method for shaping query plans and behavior without changing application code.  This feature will first be available in Azure SQL Database – including in Azure SQL single databases, elastic pools, SQL Managed Instance and Hyperscale service tier databases.

 

Query Hints

 

Ideally the Query Optimizer selects an optimal execution plan for every query in your workload.  If this doesn’t happen, a developer or DBA may choose to manually optimize for specific conditions using query hints.

 

Specified via the OPTION clause, query hints can be used to influence query execution behavior.  For example, the following query sets the max degree of parallelism to “1”:

 

SELECT COUNT(DISTINCT [WWI Order ID])
FROM [Fact].[OrderHistoryExtended]
OPTION (MAXDOP 1);

 

Applying query hints to statements requires a rewrite of the original query text.  DBAs often cannot make changes directly to the T-SQL code or may not be able to immediately make the desired changes.

 

DBAs can turn to plan guides for this scenario, but the SQL team has heard the feedback that plan guides are oftentimes too complex to use.

 

Introducing Query Store hints

 

Query Store hints provide a simple method for shaping query plans without changing the original code.

 

 

To use Query Store hints, do the following:

 

Step 1: Identify the Query Store query_id of the statement you wish to modify.  In the following example we use the Query Store catalog views to find the query_id for a query given a specific query text string:

 

SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

 

Note: You can also find a query_id from SQL Server Management Studio Query Store reports or through Query Performance Insight in the portal.

 

Step 2: Execute sp_query_store_set_hints with the query_id and query hint string you wish to apply to the query.  This query hint string can contain one or more query hints:

 

EXEC sp_query_store_set_hints 3, N'OPTION(RECOMPILE)';
GO

 

You can use the same procedure to modify existing hints and you can track all created hints in the new sys.query_store_query_hints catalog view.  

 

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason,
last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints;

 

To remove hints, use the sp_query_store_clear_hints procedure:

 

EXEC sp_query_store_clear_hints @query_id = 3;
GO

 

The documentation link (https://aka.ms/querystorehintsdocs) goes into more details on Query Store hints, but a few key points to get you started for the first version of this feature:

  • Query Store hints support statement-level hints.
  • Query Store hints are persisted and survive restarts and failovers.
  • Query Store hints override any existing hard-coded statement level hints and plan guide hints.
  • If hints contradict what is possible, we will not block query execution and the Query Store hint will not be applied.

 

Learning more about Query Store hints

 

Query Store hints provide a short-term, easy-to-apply fix you can use when you cannot directly or immediately change the text of the query.  Many of the common query hints customers use today are enabled in the public preview of Query Store hints.  Example use-cases:

  • Recompile a query on each execution
  • Cap the memory grant size for a bulk operation
  • Limit maximum degree of parallelism 
  • Disable "row goal" for a TOP query
  • Use a different join algorithm 
  • Use a lower database compatibility level
  • Use the legacy cardinality estimator

To learn more details about Query Store hints, see the full documentation at https://aka.ms/querystorehintsdocs

 

Have feedback?  During public preview, you can email QSHintsFeedback@microsoft.com. Or if you prefer, log your feedback in the SQL Database User Voice forum.

 

 

 

Updated Jun 09, 2021
Version 1.0
  • jchang6's avatar
    jchang6
    Copper Contributor

    1. suppose I have a stored proc or parameterized sql that is executed frequently - that I do not want a recompile every time. Suppose parameters include @StartDate , @EndDate

    If start - end dates is 1 or 2 days, then I want one plan, but a different plan for multi-day inputs. The first situation should use a precompiled plan for narrow date range. The second could have a precompiled plan for wide range or get a recompile because its going to be an expensive query anyways and compile is no big deal.

    2. Complex query with unavoidable propagating row estimate deviation. Could there be a hint to evaluate the small tables (having a SARG specified) to estimate the rows in the big tables, and base the plan optimization on that? One option, I write a query with a series of CTE's, and have a hint to actual evaluate the rows in one or more CTE's to optimize the plan for the final query?

    3. Intelligent query processing is great, but plans derived from a cost model based on disk IO (random vs. sequential) is very different from when data is in memory. It is time to change the execution plan cost model