| title | sys.query_store_runtime_stats (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | sys.query_store_runtime_stats (Transact-SQL) | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 09/19/2022 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| ms.custom | event-tier1-build-2022 | ||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | =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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] will always return zero (0). |
| last_cpu_time | bigint | Last CPU time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] will always return zero (0). |
| min_cpu_time | bigint | Minimum CPU time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] will always return zero (0). |
| max_cpu_time | bigint | Maximum CPU time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] will always return zero (0). |
| avg_clr_time | float | Average CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] will always return zero (0). |
| last_clr_time | bigint | Last CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] will always return zero (0). |
| min_clr_time | bigint | Minimum CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] will always return zero (0). |
| max_clr_time | bigint | Maximum CLR time for the query plan within the aggregation interval (reported in microseconds). Note: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] will always return zero (0). |
| avg_dop | float | Average DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] will always return zero (0). |
| last_dop | bigint | Last DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] will always return zero (0). |
| min_dop | bigint | Minimum DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] will always return zero (0). |
| max_dop | bigint | Maximum DOP (degree of parallelism) for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] will always return zero (0). |
| stdev_dop | float | DOP (degree of parallelism) standard deviation for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] will always return zero (0). |
| avg_rowcount | float | Average number of returned rows for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] will always return zero (0). |
| min_rowcount | bigint | Minimum number of returned rows for the query plan within the aggregation interval. Note: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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: [!INCLUDEssSDW] 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 [!INCLUDEssSDSfull]. Note: [!INCLUDEssSDW] 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 [!INCLUDEssSDSfull]. Note: [!INCLUDEssSDW] 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 [!INCLUDEssSDSfull]. Note: [!INCLUDEssSDW] 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 [!INCLUDEssSDSfull]. Note: [!INCLUDEssSDW] 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 [!INCLUDEssSDSfull]. Note: [!INCLUDEssSDW] 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 [!INCLUDEssSDSfull]. |
| 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 [!INCLUDEssSDSfull]. |
| 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 [!INCLUDEssSDSfull]. |
| 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 [!INCLUDEssSDSfull]. |
| 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 [!INCLUDEssSDSfull]. |
| 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: [!INCLUDEssSDW], [!INCLUDEssSDSfull], [!INCLUDEssSDSMIfull] (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: [!INCLUDEssSDSfull] Hyperscale Note: [!INCLUDEssSDW], [!INCLUDEssSDSfull], [!INCLUDEssSDSMIfull] (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: [!INCLUDEssSDSfull] Hyperscale Note: [!INCLUDEssSDW], [!INCLUDEssSDSfull], [!INCLUDEssSDSMIfull] (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: [!INCLUDEssSDSfull] Hyperscale Note: [!INCLUDEssSDW], [!INCLUDEssSDSfull], [!INCLUDEssSDSMIfull] (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: [!INCLUDEssSDSfull] Hyperscale Note: [!INCLUDEssSDW], [!INCLUDEssSDSfull], [!INCLUDEssSDSMIfull] (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.
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