sys.query_store_query (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure 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).
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).
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.
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.
query_parameterization_type tinyint Kind of parameterization:

0 – None

1 – User

2 – Simple

3 – Forced
query_parameterization_type_desc 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 bigint Information that can be provided to sys.dm_exec_sql_text along with last_compile_batch_sql_handle.
last_compile_batch_offset_end bigint Information that can be provided to sys.dm_exec_sql_text along with last_compile_batch_sql_handle.
count_compiles bigint Compilation statistics.
avg_compile_duration float Compilation statistics in microseconds.
last_compile_duration bigint Compilation statistics in microseconds.
avg_bind_duration float Binding statistics in microseconds.
last_bind_duration bigint Binding statistics.
avg_bind_cpu_time float Binding statistics.
last_bind_cpu_time bigint Binding statistics.
avg_optimize_duration float Optimization statistics in microseconds.
last_optimize_duration bigint Optimization statistics.
avg_optimize_cpu_time float Optimization statistics in microseconds.
last_optimize_cpu_time bigint Optimization statistics.
avg_compile_memory_kb float Compile memory statistics.
last_compile_memory_kb bigint Compile memory statistics.
max_compile_memory_kb bigint Compile memory statistics.
is_clouddb_internal_query bit Always 0 in SQL Server on-premises.

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)