Redigera

Dela via

sys.query_store_query_hints (Transact-SQL)

  • Artikel

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 the query_id column in sys.query_store_query.
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. Includes the message_id of the error message.
last_query_hint_failure_reason_desc nvarchar(128) Includes the error description of the error message.
query_hint_failure_count bigint Number of times that the query hint application 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 is 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 and removed by sys.sp_query_store_clear_hints.

Permissions

SQL Server 2019 (15.x) and previous versions require VIEW SERVER STATE permission on the server.

SQL Server 2022 (16.x) and later versions require VIEW SERVER PERFORMANCE STATE permission on the server.

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;

Related content