Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server 2022 (16.x)
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
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).
Caution
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.
Watch this video for an overview of Query Store hints:
Ideally, the Query Optimizer selects an optimal execution plan for a query.
If an optimal plan isn't selected, a developer or DBA might 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 might not always be able to make changes directly to Transact-SQL code to inject a query hint. The Transact-SQL might be hard-coded into an application or automatically generated by the application. Previously, a developer might 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.
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. Query Store greatly simplifies the overall performance tuning customer experience. SQL Server 2016 (13.x) first introduced Query Store, and now it's enabled by default in SQL Server 2022 (16.x), Azure SQL Managed Instance, and Azure SQL Database.
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:
To use Query Store hints:
query_id
of the query statement you wish to modify. You can do this in various ways:
sys.sp_query_store_set_hints
with the query_id
and 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, query execution is not blocked and the hint isn't 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.
To create or update hints, use sys.sp_query_store_set_hints. Hints are specified in a valid string format N'OPTION (...)'
.
query_id
, a new Query Store hint is created.query_id
, the last value provided overrides previously specified values for the associated query.query_id
doesn't exist, an error is raised.Note
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.
When hints are applied, the following result set appears in the StmtSimple
element of the Execution Plan in XML format:
Attribute | Description |
---|---|
QueryStoreStatementHintText |
Actual Query Store hint(s) applied to the query |
QueryStoreStatementHintId |
Unique identifier of a query hint |
QueryStoreStatementHintSource |
Source of Query Store hint (ex: "User") |
Note
These XML elements are available via the output of the Transact-SQL commands SET STATISTICS XML and SET SHOWPLAN XML.
Query Store hints have no effect on secondary replicas unless Query Store for secondary replicas is enabled. For more information, see Query Store for secondary replicas.
@query_hint_scope
parameter, which was introduced in SQL Server 2022 (16.x).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
The following example queries sys.query_store_query_text and sys.query_store_query to return the query_id
for an executed query text fragment.
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;
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 query_id
39.
Once identified, apply the hint to enforce a maximum memory grant size in percent of configured memory limit to the query_id
:
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 query_id
39:
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;
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Documentation
Query Store hints best practices - SQL Server
Best practices for the Query Store hints feature, which helps you to shape query plans without changing application code.
Monitor performance by using the Query Store - SQL Server
Query Store provides insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. Query Store captures history of queries, plans, and runtime statistics.
Optimized plan forcing with Query Store - SQL Server
Learn about optimized plan forcing and optimization replay scripts in Query Store.