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]) 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
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: