sys.query_store_query (Transact-SQL)

APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

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 SQL Data Warehouse will always return zero (0).
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 SQL Data Warehouse 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 SQL Data Warehouse 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 SQL Data Warehouse will always return zero (0).
query_parameterization_type tinyint Kind of parameterization:

0 - None

1 - User

2 - Simple

3 - Forced
Note: Azure SQL Data Warehouse will always return zero (0).
query_parameterization_type_desc nvarchar(60) Textual description for the parameterization type.
Note: Azure SQL Data Warehouse 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.
Note: Azure SQL Data Warehouse will always return NULL.
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 SQL Data Warehouse 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 SQL Data Warehouse will always return zero (0).
count_compiles bigint Compilation statistics.
Note: Azure SQL Data Warehouse 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 SQL Data Warehouse will always return zero (0).
last_bind_duration bigint Binding statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
avg_bind_cpu_time float Binding statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
last_bind_cpu_time bigint Binding statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
avg_optimize_duration float Optimization statistics in microseconds.
Note: Azure SQL Data Warehouse will always return zero (0).
last_optimize_duration bigint Optimization statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
avg_optimize_cpu_time float Optimization statistics in microseconds.
Note: Azure SQL Data Warehouse will always return zero (0).
last_optimize_cpu_time bigint Optimization statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
avg_compile_memory_kb float Compile memory statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
last_compile_memory_kb bigint Compile memory statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
max_compile_memory_kb bigint Compile memory statistics.
Note: Azure SQL Data Warehouse will always return zero (0).
is_clouddb_internal_query bit Always 0 in SQL Server on-premises.
Note: Azure SQL Data Warehouse will always return zero (0).

Permissions

Requires the VIEW DATABASE STATE permission.

See Also

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)
Monitoring Performance By Using the Query Store
Catalog Views (Transact-SQL)
Query Store Stored Procedures (Transact-SQL)
sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)