Edit

Share via

sys.query_store_query (Transact-SQL)

  • Article

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

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 1 bigint Foreign key. Joins to sys.query_context_settings (Transact-SQL).
object_id 2 bigint ID of the database object that the query is part of (stored procedure, trigger, CLR UDF/UDAgg, etc.). 0 if the query isn't executed as part of a database object (ad hoc query).
batch_sql_handle 3 varbinary(64) ID of the statement batch the query is part of. Populated only if query references temporary tables or table variables.
query_hash binary(8) Zobrist hash over the shape of the individual query, based on the bound (input) logical query tree. Query hints aren't included as part of the hash.
is_internal_query 2 bit The query was generated internally.
query_parameterization_type 2 tinyint Type of parameterization:

0 - None
1 - User
2 - Simple
3 - Forced
query_parameterization_type_desc 4 nvarchar(60) Textual description for the parameterization type.
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 2 bigint Information that can be provided to sys.dm_exec_sql_text along with last_compile_batch_sql_handle.
last_compile_batch_offset_end 2 bigint Information that can be provided to sys.dm_exec_sql_text along with last_compile_batch_sql_handle.
count_compiles 1 bigint Compilation statistics.
avg_compile_duration float Compilation statistics in microseconds.
last_compile_duration bigint Compilation statistics in microseconds.
avg_bind_duration 2 float Binding statistics in microseconds.
last_bind_duration 2 bigint Binding statistics.
avg_bind_cpu_time 2 float Binding statistics.
last_bind_cpu_time 2 bigint Binding statistics.
avg_optimize_duration float Optimization statistics in microseconds.
last_optimize_duration bigint Optimization statistics.
avg_optimize_cpu_time 2 float Optimization statistics in microseconds.
last_optimize_cpu_time 2 bigint Optimization statistics.
avg_compile_memory_kb 2 float Compile memory statistics.
last_compile_memory_kb 2 bigint Compile memory statistics.
max_compile_memory_kb 2 bigint Compile memory statistics.
is_clouddb_internal_query 2 bit Always 0 in SQL Server on-premises.

1 Azure Synapse Analytics always returns one (1).

2 Azure Synapse Analytics always returns zero (0).

3 Azure Synapse Analytics always returns NULL.

4 Azure Synapse Analytics always returns None.

Permissions

Requires the VIEW DATABASE STATE permission.

Related content