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.
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])
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.
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%';
|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)';
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
To remove hints, use the sp_query_store_clear_hints procedure:
EXEC sp_query_store_clear_hints @query_id = 3;
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:
To learn more details about Query Store hints, see the full documentation at https://aka.ms/querystorehintsdocs.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.