Επεξεργασία

Κοινή χρήση μέσω


sys.query_store_runtime_stats (Transact-SQL)

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

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

Column name Data type Description
runtime_stats_id bigint Identifier of the row that represents 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.
Note: Azure Synapse Analytics will always return zero (0).
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. This is the end time of the query execution.
last_execution_time datetimeoffset Last execution time for the query plan within the aggregation interval. This is the end time of the query execution.
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).
Note: Azure Synapse Analytics will always return zero (0).
last_cpu_time bigint Last CPU time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
min_cpu_time bigint Minimum CPU time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
max_cpu_time bigint Maximum CPU time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
stdev_cpu_time float CPU time standard deviation for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
avg_logical_io_reads float Average number of logical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
last_logical_io_reads bigint Last number of logical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
min_logical_io_reads bigint Minimum number of logical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
max_logical_io_reads bigint Maximum number of logical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
stdev_logical_io_reads float Number of logical I/O reads standard deviation for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
avg_logical_io_writes float Average number of logical I/O writes for the query plan within the aggregation interval (expressed as a number of 8-KB pages written).
Note: Azure Synapse Analytics will always return zero (0).
last_logical_io_writes bigint Last number of logical I/O writes for the query plan within the aggregation interval (expressed as a number of 8-KB pages written).
Note: Azure Synapse Analytics will always return zero (0).
min_logical_io_writes bigint Minimum number of logical I/O writes for the query plan within the aggregation interval (expressed as a number of 8-KB pages written).
Note: Azure Synapse Analytics will always return zero (0).
max_logical_io_writes bigint Maximum number of logical I/O writes for the query plan within the aggregation interval (expressed as a number of 8-KB pages written).
Note: Azure Synapse Analytics will always return zero (0).
stdev_logical_io_writes float Number of logical I/O writes standard deviation for the query plan within the aggregation interval (expressed as a number of 8-KB pages written).
Note: Azure Synapse Analytics will always return zero (0).
avg_physical_io_reads float Average number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
last_physical_io_reads bigint Last number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
min_physical_io_reads bigint Minimum number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
max_physical_io_reads bigint Maximum number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
stdev_physical_io_reads float Number of physical I/O reads standard deviation for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).
Note: Azure Synapse Analytics will always return zero (0).
avg_clr_time float Average CLR time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
last_clr_time bigint Last CLR time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
min_clr_time bigint Minimum CLR time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
max_clr_time bigint Maximum CLR time for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
stdev_clr_time float CLR time standard deviation for the query plan within the aggregation interval (reported in microseconds).
Note: Azure Synapse Analytics will always return zero (0).
avg_dop float Average DOP (degree of parallelism) for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
last_dop bigint Last DOP (degree of parallelism) for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
min_dop bigint Minimum DOP (degree of parallelism) for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
max_dop bigint Maximum DOP (degree of parallelism) for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
stdev_dop float DOP (degree of parallelism) standard deviation for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
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.
Note: Azure Synapse Analytics will always return zero (0).
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.
Note: Azure Synapse Analytics will always return zero (0).
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.
Note: Azure Synapse Analytics will always return zero (0).
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.
Note: Azure Synapse Analytics will always return zero (0).
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.
Note: Azure Synapse Analytics will always return zero (0).
avg_rowcount float Average number of returned rows for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
last_rowcount bigint Number of returned rows by the last execution of the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
min_rowcount bigint Minimum number of returned rows for the query plan within the aggregation interval.
Note: Azure Synapse Analytics will always return zero (0).
max_rowcount bigint Maximum number of returned rows for the query plan within the aggregation interval.
stdev_rowcount float Standard deviation of the number of returned rows for the query plan within the aggregation interval.
avg_num_physical_io_reads float Average number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of read I/O operations).
Note: Azure Synapse Analytics will always return zero (0).
last_num_physical_io_reads bigint Last number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of read I/O operations).
Note: Azure Synapse Analytics will always return zero (0).
min_num_physical_io_reads bigint Minimum number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of read I/O operations).
Note: Azure Synapse Analytics will always return zero (0).
max_num_physical_io_reads bigint Maximum number of physical I/O reads for the query plan within the aggregation interval (expressed as a number of read I/O operations).
Note: Azure Synapse Analytics will always return zero (0).
avg_log_bytes_used float Average number of bytes in the database log used by the query plan, within the aggregation interval.
Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
Note: Azure Synapse Analytics will always return zero (0).
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 to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
Note: Azure Synapse Analytics will always return zero (0).
min_log_bytes_used bigint Minimum number of bytes in the database log used by the query plan, within the aggregation interval.
Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
Note: Azure Synapse Analytics will always return zero (0).
max_log_bytes_used bigint Maximum number of bytes in the database log used by the query plan, within the aggregation interval.
Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
Note: Azure Synapse Analytics will always return zero (0).
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 to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
Note: Azure Synapse Analytics will always return zero (0).
avg_tempdb_space_used float Average number of pages used in tempdb for the query plan within the aggregation interval (expressed as a number of 8-KB pages).

Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
last_tempdb_space_used bigint Last number of pages used in tempdb for the query plan within the aggregation interval (expressed as a number of 8-KB pages).

Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
min_tempdb_space_used bigint Minimum number of pages used in tempdb for the query plan within the aggregation interval (expressed as a number of 8-KB pages).

Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
max_tempdb_space_used bigint Maximum number of pages used in tempdb for the query plan within the aggregation interval (expressed as a number of 8-KB pages).

Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
stdev_tempdb_space_used float Number of pages used in tempdb standard deviation for the query plan within the aggregation interval (expressed as a number of 8-KB pages).

Applies to: SQL Server (Starting with SQL Server 2017 (14.x)) and Azure SQL Database.
avg_page_server_io_reads float Average number of page server I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).

Applies to: Azure SQL Database Hyperscale
Note: Azure Synapse Analytics, Azure SQL Database, Azure SQL Managed Instance (non-hyperscale) will always return zero (0).
last_page_server_io_reads bigint Last number of page server I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).

Applies to: Azure SQL Database Hyperscale
Note: Azure Synapse Analytics, Azure SQL Database, Azure SQL Managed Instance (non-hyperscale) will always return zero (0).
min_page_server_io_reads bigint Minimum number of page server I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).

Applies to: Azure SQL Database Hyperscale
Note: Azure Synapse Analytics, Azure SQL Database, Azure SQL Managed Instance (non-hyperscale) will always return zero (0).
max_page_server_io_reads bigint Maximum number of page server I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).

Applies to: Azure SQL Database Hyperscale
Note: Azure Synapse Analytics, Azure SQL Database, Azure SQL Managed Instance (non-hyperscale) will always return zero (0).
stdev_page_server_io_reads float Standard deviation of the number of page server I/O reads for the query plan within the aggregation interval (expressed as a number of 8-KB pages read).

Applies to: Azure SQL Database Hyperscale
Note: Azure Synapse Analytics, Azure SQL Database, Azure SQL Managed Instance (non-hyperscale) will always return zero (0).
replica_group_id bigint Identifies the replica set number for this replica. Foreign key to sys.query_store_replicas.

Applies to: SQL Server (Starting with SQL Server 2022 (16.x))

Permissions

Requires the VIEW DATABASE STATE permission.

Remarks

In rare situations, you might notice the DOP columns (max_dop, min_dop, avg_dop and last_dop) report large numbers. This might happen when queries execute on systems with large number of processors. You might notice this discrepancy in scenarios where the query uses user defined functions. The engineering team is investigating this issue and will update this note when this issue is fixed. This is a reporting issue in the statistics gathered for this catalog view and does not impact the actual query execution or its runtime performance.

Next steps

Learn more about Query Store in the following articles: