| title | sys.query_store_query (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | sys.query_store_query (Transact-SQL) | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 10/14/2021 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | =azuresqldb-current||>=sql-server-2016||=azure-sqldw-latest||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sqlserver2016-asdb-asdbmi-asa]
Contains information about the query and its associated overall aggregated runtime execution statistics.
| Column name | Data type | Description |
|---|---|---|
| query_id | bigint | Primary key. |
| query_text_id | bigint | Foreign key. Joins to sys.query_store_query_text (Transact-SQL) |
| context_settings_id | bigint | Foreign key. Joins to sys.query_context_settings (Transact-SQL). Note: Azure Synapse Analytics will always return one (1). |
| object_id | bigint | ID of the database object that the query is part of (stored procedure, trigger, CLR UDF/UDAgg, etc.). 0 if the query is not executed as part of a database object (ad-hoc query). Note: Azure Synapse Analytics will always return zero (0). |
| batch_sql_handle | varbinary(64) | ID of the statement batch the query is part of. Populated only if query references temporary tables or table variables. Note: Azure Synapse Analytics will always return NULL. |
| query_hash | binary(8) | MD5 hash of the individual query, based on the logical query tree. Includes optimizer hints. |
| is_internal_query | bit | The query was generated internally. Note: Azure Synapse Analytics will always return zero (0). |
| query_parameterization_type | tinyint | Kind of parameterization: 0 - None 1 - User 2 - Simple 3 - Forced Note: Azure Synapse Analytics will always return zero (0). |
| query_parameterization_type_desc | nvarchar(60) | Textual description for the parameterization type. Note: Azure Synapse Analytics will always return None. |
| initial_compile_start_time | datetimeoffset | Compile start time. |
| last_compile_start_time | datetimeoffset | Compile start time. |
| last_execution_time | datetimeoffset | Last execution time refers to the last end time of the query/plan. |
| last_compile_batch_sql_handle | varbinary(64) | Handle of the last SQL batch in which query was used last time. It can be provided as input to sys.dm_exec_sql_text (Transact-SQL) to get the full text of the batch. |
| last_compile_batch_offset_start | bigint | Information that can be provided to sys.dm_exec_sql_text along with last_compile_batch_sql_handle. Note: Azure Synapse Analytics will always return zero (0). |
| last_compile_batch_offset_end | bigint | Information that can be provided to sys.dm_exec_sql_text along with last_compile_batch_sql_handle. Note: Azure Synapse Analytics will always return zero (0). |
| count_compiles | bigint | Compilation statistics. Note: Azure Synapse Analytics will always return one (1). |
| avg_compile_duration | float | Compilation statistics in microseconds. |
| last_compile_duration | bigint | Compilation statistics in microseconds. |
| avg_bind_duration | float | Binding statistics in microseconds. Note: Azure Synapse Analytics will always return zero (0). |
| last_bind_duration | bigint | Binding statistics. Note: Azure Synapse Analytics will always return zero (0). |
| avg_bind_cpu_time | float | Binding statistics. Note: Azure Synapse Analytics will always return zero (0). |
| last_bind_cpu_time | bigint | Binding statistics. Note: Azure Synapse Analytics will always return zero (0). |
| avg_optimize_duration | float | Optimization statistics in microseconds. |
| last_optimize_duration | bigint | Optimization statistics. |
| avg_optimize_cpu_time | float | Optimization statistics in microseconds. Note: Azure Synapse Analytics will always return zero (0). |
| last_optimize_cpu_time | bigint | Optimization statistics. Note: Azure Synapse Analytics will always return zero (0). |
| avg_compile_memory_kb | float | Compile memory statistics. Note: Azure Synapse Analytics will always return zero (0). |
| last_compile_memory_kb | bigint | Compile memory statistics. Note: Azure Synapse Analytics will always return zero (0). |
| max_compile_memory_kb | bigint | Compile memory statistics. Note: Azure Synapse Analytics will always return zero (0). |
| is_clouddb_internal_query | bit | Always 0 in [!INCLUDEssNoVersion] on-premises. Note: Azure Synapse Analytics will always return zero (0). |
Requires the VIEW DATABASE STATE permission.
sys.database_query_store_options (Transact-SQL)
sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)
sys.query_store_query_text (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)
sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_runtime_stats_interval (Transact-SQL)
sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)
Query Store hints
Monitoring Performance By Using the Query Store
Catalog Views (Transact-SQL)
Query Store Stored Procedures (Transact-SQL)