Muokkaa

Jaa

sys.sp_query_store_set_hints (Transact-SQL)

  • Artikkeli

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

Creates or updates Query Store hints for a given query_id.

Transact-SQL syntax conventions

Syntax

sp_query_store_set_hints
    [ @query_id = ] query_id ,
    [ @query_hints = ] 'query_hints'
    [ , [ @query_hint_scope = ] 'replica_group_id' ]
[ ; ]

Arguments

Important

Arguments for extended stored procedures must be entered in the specific order as described in the Syntax section. If the parameters are entered out of order, an error message occurs.

[ @query_id = ] query_id

The Query Store query_id column from sys.query_store_query. @query_id is bigint.

[ @query_hints = ] N'query_hints'

A character string of query options beginning with OPTION. @query_hints is nvarchar(max). For more information, see Supported query hints in this article.

[ @query_hint_scope = ] 'replica_group_id'

By default, the scope of a new Query Store hint is the local replica only. @query_hint_scope is tinyint. This optional parameter determines the scope at which the hint will be applied on a secondary replica when Query Store for secondary replicas is enabled. The optional query_hint_scope argument defaults only to the local replica (primary or secondary), but you can optionally specify a replica_group_id referencing sys.query_store_replicas.

Return value

0 (success) or 1 (failure).

Remarks

Hints are specified in a valid T-SQL string format N'OPTION (..)'.

  • If no Query Store hint exists for a specific query_id, a new Query Store hint will be created.
  • 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_id doesn't exist, an error will be raised.

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 remove hints associated with a query_id, use the system stored procedure sys.sp_query_store_clear_hints.

Supported query hints

These query hints are supported as Query Store hints:

{ HASH | ORDER } GROUP
  | { CONCAT | HASH | MERGE } UNION
  | { LOOP | MERGE | HASH } JOIN
  | EXPAND VIEWS
  | FAST number_rows
  | FORCE ORDER
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  | KEEP PLAN
  | KEEPFIXED PLAN
  | MAX_GRANT_PERCENT = percent
  | MIN_GRANT_PERCENT = percent
  | MAXDOP number_of_processors
  | NO_PERFORMANCE_SPOOL
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN
  | USE HINT ( '<hint_name>' [ , ...n ] )

The following query hints are currently unsupported:

Permissions

Requires the ALTER permission on the database.

Examples

Identify a query in Query Store

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 example, 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.

Apply single hint

The following example applies the RECOMPILE hint to query_id 39, as identified in Query Store:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE)';

The following example applies the hint to force the legacy cardinality estimator to query_id 39, identified in Query Store:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Apply multiple hints

The following example applies multiple query hints to query_id 39, including RECOMPILE, MAXDOP 1, and the SQL 2012 query optimizer behavior:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

View Query Store hints

The following example returns existing Query Store 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
WHERE query_id = 39;

Remove the hint from a query

Use the following example to remove the hint from query_id 39, using the sp_query_store_clear_hints system stored procedure.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Related content