| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.query_store_runtime_stats (Transact-SQL) |
sys.query_store_runtime_stats (Transact-SQL) |
rwestMSFT |
randolphwest |
09/19/2022 |
sql |
system-objects |
reference |
|
|
|
=azuresqldb-current||>=sql-server-2016||=azure-sqldw-latest||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sqlserver2016-asdb-asdbmi-asa]
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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| last_cpu_time | bigint | Last CPU time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| min_cpu_time | bigint | Minimum CPU time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| max_cpu_time | bigint | Maximum CPU time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| avg_clr_time | float | Average CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| last_clr_time | bigint | Last CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| min_clr_time | bigint | Minimum CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| max_clr_time | bigint | Maximum CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| avg_dop | float | Average DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| last_dop | bigint | Last DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| min_dop | bigint | Minimum DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| max_dop | bigint | Maximum DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| stdev_dop | float | DOP (degree of parallelism) standard deviation for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| avg_rowcount | float | Average number of returned rows for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] will always return zero (0). |
| min_rowcount | bigint | Minimum number of returned rows for the query plan within the aggregation interval. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. Note: [!INCLUDEssazuresynapse-md] 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. |
| 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. |
| 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. |
| 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. |
| 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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL17]) and [!INCLUDE ssazure-sqldb]. |
| 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: [!INCLUDEssazuresynapse-md], [!INCLUDE ssazure-sqldb], [!INCLUDEssazuremi] (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: [!INCLUDE ssazure-sqldb] Hyperscale Note: [!INCLUDEssazuresynapse-md], [!INCLUDE ssazure-sqldb], [!INCLUDEssazuremi] (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: [!INCLUDE ssazure-sqldb] Hyperscale Note: [!INCLUDEssazuresynapse-md], [!INCLUDE ssazure-sqldb], [!INCLUDEssazuremi] (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: [!INCLUDE ssazure-sqldb] Hyperscale Note: [!INCLUDEssazuresynapse-md], [!INCLUDE ssazure-sqldb], [!INCLUDEssazuremi] (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: [!INCLUDE ssazure-sqldb] Hyperscale Note: [!INCLUDEssazuresynapse-md], [!INCLUDE ssazure-sqldb], [!INCLUDEssazuremi] (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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsql-server-2022]) |
Requires the VIEW DATABASE STATE permission.
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.
Learn more about Query Store in the following articles:
- sys.query_store_replicas (Transact-SQL)
- 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)
- Best Practice with the Query Store