Wednesday, June 9, 2021

Query Store Hints Preview

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.

 

query_store_hints_blog_6.PNG

 

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;

query_store_hints_blog_5.PNG

 

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.

 

 

 

Posted at https://sl.advdat.com/3gp7yJZ
Labels: ,