sys.query_store_runtime_stats (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Contains information about the runtime execution statistics information for the query.

Column name Data type Description
runtime_stats_id bigint Identifier of the row representing runtime execution statistics for the plan_id, execution_type and runtime_stats_interval_id. It is unique only for the past runtime statistics intervals. For currently active interval there may be multiple rows representing runtime statistics for the plan referenced by plan_id, with the execution type represented by execution_type. Typically, one row represents runtime statistics that are flushed to disk, while other(s) represent in-memory state. Hence, to get actual state for every interval you need to aggregate metrics, grouping by plan_id, execution_type and runtime_stats_interval_id.
plan_id bigint Foreign key. Joins to sys.query_store_plan (Transact-SQL).
runtime_stats_interval_id bigint Foreign key. Joins to sys.query_store_runtime_stats_interval (Transact-SQL).
execution_type tinyint Determines type of query execution:

0 – Regular execution (successfully finished)

3 – Client initiated aborted execution

4 - Exception aborted execution
execution_type_desc nvarchar(128) Textual description of the execution type field:

0 – Regular

3 – Aborted

4 - Exception
first_execution_time datetimeoffset First execution time for the query plan within the aggregation interval.
last_execution_time datetimeoffset Last execution time for the query plan within the aggregation interval.
count_executions bigint Total count of executions for the query plan within the aggregation interval.
avg_duration float Average duration for the query plan within the aggregation interval (reported in microseconds) .
last_duration bigint Last duration for the query plan within the aggregation interval (reported in microseconds).
min_duration bigint Minimum duration for the query plan within the aggregation interval (reported in microseconds).
max_duration bigint Maximum duration for the query plan within the aggregation interval (reported in microseconds).
stdev_duration float Duration standard deviation for the query plan within the aggregation interval (reported in microseconds).
avg_cpu_time float Average CPU time for the query plan within the aggregation interval (reported in microseconds).
last_cpu_time bigint Last CPU time for the query plan within the aggregation interval (reported in microseconds).
min_cpu_time bigint Minimum CPU time for the query plan within the aggregation interval (reported in microseconds).
max_cpu_time bigint Maximum CPU time for the query plan within the aggregation interval (reported in microseconds).
stdev_cpu_time float CPU time standard deviation for the query plan within the aggregation interval (reported in microseconds).
avg_logical_io_reads float Average number of logical IO reads for the query plan within the aggregation interval. (expressed as a number of 8KB pages read).
last_logical_io_reads bigint Last number of logical IO reads for the query plan within the aggregation interval. (expressed as a number of 8KB pages read).
min_logical_io_reads bigint Minimum number of logical IO reads for the query plan within the aggregation interval. (expressed as a number of 8KB pages read).
max_logical_io_reads bigint Maximum number of logical IO reads for the query plan within the aggregation interval.(expressed as a number of 8KB pages read).
stdev_logical_io_reads float Number of logical IO reads standard deviation for the query plan within the aggregation interval. (expressed as a number of 8KB pages read).
avg_logical_io_writes float Average number of logical IO writes for the query plan within the aggregation interval.
last_logical_io_writes bigint Last number of logical IO writes for the query plan within the aggregation interval.
min_logical_io_writes bigint Minimum number of logical IO writes for the query plan within the aggregation interval.
max_logical_io_writes bigint Maximum number of logical IO writes for the query plan within the aggregation interval.
stdev_logical_io_writes float Number of logical IO writes standard deviation for the query plan within the aggregation interval.
avg_physical_io_reads float Average number of physical IO reads for the query plan within the aggregation interval (expressed as a number of 8KB pages read).
last_physical_io_reads bigint Last number of physical IO reads for the query plan within the aggregation interval (expressed as a number of 8KB pages read).
min_physical_io_reads bigint Minimum number of physical IO reads for the query plan within the aggregation interval (expressed as a number of 8KB pages read).
max_physical_io_reads bigint Maximum number of physical IO reads for the query plan within the aggregation interval (expressed as a number of 8KB pages read).
stdev_physical_io_reads float Number of physical IO reads standard deviation for the query plan within the aggregation interval (expressed as a number of 8KB pages read).
avg_clr_time float Average CLR time for the query plan within the aggregation interval (reported in microseconds).
last_clr_time bigint Last CLR time for the query plan within the aggregation interval (reported in microseconds).
min_clr_time bigint Minimum CLR time for the query plan within the aggregation interval (reported in microseconds).
max_clr_time bigint Maximum CLR time for the query plan within the aggregation interval (reported in microseconds).
stdev_clr_time float CLR time standard deviation for the query plan within the aggregation interval (reported in microseconds).
avg_dop float Average DOP (degree of parallelism) for the query plan within the aggregation interval.
last_dop bigint Last DOP (degree of parallelism) for the query plan within the aggregation interval.
min_dop bigint Minimum DOP (degree of parallelism) for the query plan within the aggregation interval.
max_dop bigint Maximum DOP (degree of parallelism) for the query plan within the aggregation interval.
stdev_dop float DOP (degree of parallelism) standard deviation for the query plan within the aggregation interval.
avg_query_max_used_memory float Average memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Always 0 for queries using natively compiled memory optimized procedures.
last_query_max_used_memory bigint Last memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Always 0 for queries using natively compiled memory optimized procedures.
min_query_max_used_memory bigint Minimum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Always 0 for queries using natively compiled memory optimized procedures.
max_query_max_used_memory bigint Maximum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Always 0 for queries using natively compiled memory optimized procedures.
stdev_query_max_used_memory float Memory grant standard deviation (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Always 0 for queries using natively compiled memory optimized procedures.
avg_rowcount float Average number of returned rows for the query plan within the aggregation interval.
last_rowcount bigint Number of returned rows by the last execution of the query plan within the aggregation interval.
min_rowcount bigint Minimum number of returned rows for the query plan within the aggregation interval.
max_rowcount bigint Maximum number of returned rows for the query plan within the aggregation interval.
stdev_rowcount float Number of returned rows standard deviation for the query plan within the aggregation interval.
avg_log_bytes_used float Average number of bytes in the database log used by the query plan, within the aggregation interval. Applies only to Azure SQL Database.
last_log_bytes_used bigint Number of bytes in the database log used by the last execution of the query plan, within the aggregation interval. Applies only to Azure SQL Database.
min_log_bytes_used bigint Minimum number of bytes in the database log used by the query plan, within the aggregation interval. Applies only to Azure SQL Database.
max_log_bytes_used bigint Maximum number of bytes in the database log used by the query plan, within the aggregation interval. Applies only to Azure SQL Database.
stdev_log_bytes_used float Standard deviation of the number of bytes in the database log used by a query plan, within the aggregation interval. Applies only to Azure SQL Database.

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 (Transact-SQL)
sys.query_store_query_text (Transact-SQL)
sys.query_store_wait_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)