Edit

sys.query_store_query_hints (Transact-SQL)

  • Article

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

Returns query hints from Query Store hints.

Column name Data type Description
query_hint_id bigint Unique identifier of a query hint.
query_id bigint Unique identifier of a query in the Query Store. Foreign key to sys.query_store_query.query_id.)
query_hint_text nvarchar(MAX) Hint definition in form of N'OPTION (…)
last_query_hint_failure_reason int Error code returned when if applying hints failed. Will include the message_id of the error message.
last_query_hint_failure_reason_desc nvarchar(128) Will include the error description of the error message.
query_hint_failure_count bigint Number of times that the query hint application has failed since the query hint was created or last modified.
source int Source of Query Store hint: user source is zero and system-generated is non-zero.
source_desc nvarchar(128) Description of source of Query Store hint.
comment nvarchar(max) Internal use only.
query_hint_scope tinyint Determines the scope at which the hint will be applied, as per the replica_group_id column in sys.query_store_replicas.

Remarks

Query Store hints are created by sys.sp_query_store_set_hints (Transact-SQL) and removed by sys.sp_query_store_clear_hints.

Permissions

Requires the VIEW DATABASE STATE permission.

Examples

View Query Store hints

The following example returns existing Query Store hints 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;

Next steps