Query Store hints
This article outlines how to apply query hints using the Query Store. Query Store hints provide an easy-to-use method for shaping query plans without changing application code.
Query Store hints are available in Azure SQL Database and Azure SQL Managed Instance. Query Store hints are also a feature introduced to SQL Server in SQL Server 2022 (16.x).
- For more information on configuring and administering with the Query Store, see Monitoring performance by using the Query Store.
- For information on discovering actionable information and tune performance with the Query Store, see Tuning performance by using the Query Store.
- For information about operating the Query Store in Azure SQL Database, see Operating the Query Store in Azure SQL Database.
Because the SQL Server Query Optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators. For more information, see Query Hints.
Ideally the Query Optimizer selects an optimal execution plan for a query. When this doesn't happen, a developer or DBA may wish to manually optimize for specific conditions. Query hints are specified via the OPTION clause and can be used to affect query execution behavior. While query hints help provide localized solutions to various performance-related issues, they do require a rewrite of the original query text. Database administrators and developers may not always be able to make changes directly to Transact-SQL code to inject a query hint. The Transact-SQL may be hard-coded into an application or automatically generated by the application. Previously, a developer may have to rely on plan guides, which can be complex to use.
For information on which query hints can be applied, see Supported query hints.
When to use Query Store hints
As the name suggests, this feature extends and depends on the Query Store. Query Store enables the capturing of queries, execution plans, and associated runtime statistics. Introduced in SQL Server 2016 (13.x) and on-by-default in Azure SQL Database, Query Store greatly simplifies the overall performance tuning customer experience.
First the query is executed, then captured by Query Store. Then the DBA creates a Query Store hint on a query. Thereafter, the query is executed using the Query Store hint.
Examples where Query Store hints can help with query-level performance issues:
- Recompile a query on each execution.
- Cap the memory grant size for a bulk insert operation.
- Limit the maximum degree of parallelism when updating statistics.
- Use a Hash join instead of a Nested Loops join.
- Use compatibility level 110 for a specific query while keeping everything else in the database at compatibility level 150.
- Disable row goal optimization for a SELECT TOP query.
To use Query Store hints:
- Identify the Query Store
query_idof the query statement you wish to modify. You can do this in various ways: 1.1. Querying the Query Store catalog views. 1.2. Using SQL Server Management Studio built-in Query Store reports. 1.3. Using Azure portal Query Performance Insight for Azure SQL Database.
query_idand query hint string you wish to apply to the query. This string can contain one or more query hints. For complete information, see sys.sp_query_store_set_hints.
Once created, Query Store hints are persisted and survive restarts and failovers. Query Store hints override hard-coded statement-level hints and existing plan guide hints.
If a query hint contradicts what is possible for query optimization, the hint won't block query execution and the hint won't be applied. In the cases where a hint would cause a query to fail, the hint is ignored and the latest failure details can be viewed in sys.query_store_query_hints.
Watch this video for an overview of Query Store hints:
Query Store hints system stored procedures
To create or update hints, use sys.sp_query_store_set_hints. Hints are specified in a valid string format N'OPTION (...)'.
- When creating a Query Store hint, if no Query Store hint exists for a specific
query_id, a new Query Store hint will be created.
- When creating or updating a Query Store hint, if a Query Store hint already exists for a specific
query_id, the last value provided will override previously specified values for the associated query.
- If a
query_iddoesn't exist, an error will be raised.
For a complete list of hints that are supported, see sys.sp_query_store_set_hints.
To remove hints associated with a
query_id, use sys.sp_query_store_clear_hints.
Execution Plan XML attributes
||Actual Query Store hint(s) applied to the query|
||Unique identifier of a query hint|
||Source of Query Store hint (ex: "User")|
Query Store hints and feature interoperability
- Query Store hints will override other hard-coded statement level hints and plan guides.
- Queries will always execute where any opposing Query Store hints, that would otherwise cause an error, will be ignored.
- If Query Store hints contradict, SQL Server will not block query execution and Query Store hint will not be applied.
- Simple parameterization - Query Store hints are not supported for statements that qualify for simple parameterization.
- Forced parameterization - The RECOMPILE hint is not compatible with forced parameterization set at the database level. If the database has forced parameterization set, and the RECOMPILE hint is part of the hints string set in Query Store for a query, SQL Server will ignore the RECOMPILE hint and will apply any other hints if they are applied.
- Additionally, SQL Server will issue a warning (error code 12461) stating that the RECOMPILE hint was ignored.
- For more information on forced parameterization use case considerations, see Guidelines for Using Forced Parameterization.
- Manually created Query Store hints are exempt from cleanup. The hint and the query will not be cleaned up from Query Store by the automatic retention of the capture policy.
- Queries can be manually removed by users, which would also remove the associated Query Store hint.
- Query Store hints automatically generated by the CE Feedback are subject to clean up by the automatic retention of the capture policy.
- DOP feedback and memory grant feedback shape query behavior without using Query Store hints. When queries are cleanup by automatic retention of the capture policy, DOP feedback and memory grant feedback data is also cleaned up.
- You can manually create the same Query Store hint that CE feedback implemented, and then the query with the hint would no longer be subject to clean up by the automatic retention of the capture policy.
Query Store hints and availability groups
For more information, see Query Store for secondary replicas.
- Prior to SQL Server 2022 (16.x), Query Store hints can be applied against the primary replica of an availability group.
- Starting with SQL Server 2022 (16.x), when Query Store for secondary replicas is enabled, Query Store hints are also replica-aware for secondary replicas in availability groups.
- You can add a Query Store hint to a specific replica or replica set when you have Query Store for secondary replicas enabled. In sys.sp_query_store_set_query_hints, this is set by the
@query_hint_scopeparameter, which was introduced in SQL Server 2022 (16.x).
- Find the available replica sets by querying sys.query_store_replicas.
- Find plans forced on secondary replicas with sys.query_store_plan_forcing_locations.
Query Store hints best practices
- Complete index and statistics maintenance before evaluating queries for potential new Query Store hints.
- Test your application database on the latest compatibility level before using Query Store hints.
- For example, Parameter Sensitive Plan (PSP) optimization was introduced in SQL Server 2022 (16.x) (compatibility level 160), which leverages multiple active plans per query to address non-uniform data distributions. If your environment cannot use the latest compatibility level, Query Store hints using the RECOMPILE hint can be leveraged on any supporting compatibility level.
- Query Store hints override SQL Server query plan behavior. It is recommended to only leverage Query Store hints when it is necessary to address performance related issues.
- It is recommended to reevaluate Query Store hints, statement level hints, plan guides, and Query Store forced plans any time data distributions change and during database migrations projects. Changes in data distribution may cause Query Store hints to generate suboptimal execution plans.
A. Query Store hints demo
The following walk-through of Query Store hints in Azure SQL Database uses an imported database via a BACPAC file (.bacpac). Learn how to import a new database to an Azure SQL Database server, see Quickstart: Import a BACPAC file to a database.
-- ************************************************************************ -- -- Query Store hints demo -- Demo uses "PropertyMLS" database which can be imported from BACPAC here: -- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store -- Email QSHintsFeedback@microsoft.com for questions\feedback -- ************************************************************************ -- /* Demo prep, connect to the PropertyMLS database */ ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR; ALTER DATABASE CURRENT SET QUERY_STORE = ON; ALTER DATABASE CURRENT SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL); GO -- Should be READ_WRITE SELECT actual_state_desc FROM sys.database_query_store_options; GO /* You can verify Query Store Hints in sys.query_store_query_hints. Checking if any already exist (should be none). */ 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; GO /* The PropertySearchByAgent stored procedure has a parameter used to filter AgentId. Looking at the statistics for AgentId, you will see that there is a big skew for AgentId 101. */ SELECT hist.range_high_key AS [AgentId], hist.equal_rows FROM sys.stats AS s CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist WHERE s.[name] = N'NCI_Property_AgentId'; -- Show actual query execution plan to see plan compiled. -- Agent with many properties will have a scan with parallelism. EXEC [dbo].[PropertySearchByAgent] 101; -- Agents with few properties still re-use this plan (assuming no recent plan eviction). EXEC [dbo].[PropertySearchByAgent] 4; /* Now let's find the query_id associated with this query. */ 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 /* We can set the hint associated with the query_id returned in the previous result set, as below. Note, we can designate one or more query hints */ EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)'; GO /* You can verify Query Store Hints in sys.query_store_query_hints */ 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; GO -- Execute both at the same time and show actual query execution plan. -- You should see two different plans, one for AgentId 101 and one for AgentId 4. EXEC [dbo].[PropertySearchByAgent] 101; EXEC [dbo].[PropertySearchByAgent] 4; GO 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; GO /* We can remove the hint using sp_query_store_clear_query_hints */ EXEC sp_query_store_clear_hints @query_id = 10; GO /* That Query Store Hint is now removed */ 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; GO -- Execute both at the same time and show actual query execution plan. -- You should see one plan again. EXEC [dbo].[PropertySearchByAgent] 101; EXEC [dbo].[PropertySearchByAgent] 4; GO
B. Identify a query in Query Store
In this demo, the query we're attempting to tune is in the
SalesLT sample database:
SELECT * FROM SalesLT.Address as A INNER JOIN SalesLT.CustomerAddress as CA on A.AddressID = CA.AddressID WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;
Note that Query Store doesn't immediately reflect query data to its system views.
Identify the query in the query store system catalog views:
SELECT q.query_id, qt.query_sql_text 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'%PostalCode =%' AND query_sql_text not like N'%query_store%'; GO
In the following samples, the previous query example in the
SalesLT database was identified as
Once identified, apply the hint to enforce a maximum memory grant size in percent of configured memory limit to the
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';
You can also apply query hints with the following syntax, for example the option to force the legacy cardinality estimator:
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
You can apply multiple query hints with a comma-separated list:
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';
Review the Query Store hint in place for
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 WHERE query_id = 39;
Finally, remove the hint from
query_id 39, using sp_query_store_clear_hints.
EXEC sys.sp_query_store_clear_hints @query_id = 39;
- sys.query_store_query_hints (Transact-SQL)
- sys.sp_query_store_set_hints (Transact-SQL)
- sys.sp_query_store_clear_hints (Transact-SQL)
- Save an Execution Plan in XML Format
- Display and Save Execution Plans
- Hints (Transact-SQL) - Query