Query Store introduces eight new catalog views to SQL Server where it stores all the information you need to query data to troubleshoot SQL Server with Query Store and check the configuration of Query Store. You need VIEW DATABASE STATE permission to query the catalog views. In this chapter you will find descriptions of all the catalog views and their columns then examples of the queries behind the standard Query Store reports we discussed in Chapter 4.
sys.database_query_store_options
Check Query Store options across all databases on a SQL Server instance
Column listing and descriptions for sys.database_query_store options
Column name | Data type | Description |
---|---|---|
desired_state | smallint | Shows the desired state of Query Store. These are the only values that can be set by the user. Valid values are as follows: 0 = OFF 1 = READ_ONLY 2 = READ_WRITE |
desired_state_desc | nvarchar(60) | Gives you a description of the desired state of Query Store. Valid values are as follows: OFF READ_ONLY READ_WRITE |
actual_state | smallint | Shows the actual state of Query Store. Note the additional value of ERROR that was not available in the desired state. Valid values are as follows: 0 = OFF 1 = READ_ONLY 2 = READ_WRITE 3 = ERROR |
actual_state_desc | nvarchar(60) | Gives you a description of the actual state of Query Store. Valid values are as follows: OFF READ_ONLY READ_WRITE ERROR |
Column name | Data type | Description |
readonly_reason | int | When the database is in READ_ONLY mode, but the desired state is READ_WRITE, a bitmap is stored in the column to indicate the reason why. Valid values are as follows: 1 – the database has been placed in the read-only mode 2 – the database has been placed in the single-user mode 4 – the database has been placed in the emergency mode 8 – the database is a secondary replica. This applies for Availability Groups and Azure SQL Database geo-replicated databases because essentially all secondary replicas are read-only copies of the databases 65536 – the max size limit specified in MAX_STORAGE_SIZE_MB has been reached 46410 – in Azure SQL Database, there is a limit as to how many queries can be stored in internal memory, so this indicates you have reached that limit. You may upgrade to a higher service tier to get a higher limit or remove queries you no longer need from Query Store used in stored procedure sys.sp_query_store_remove_query 262144 – In Azure SQL Database there is the potential to reach a limit to the amount of data that will be held in memory before it persists to disk. During that time Query Store will be temporarily put in READ_ONLY mode 524288 – in Azure SQL Database, the database has run out of space To fix any of these conditions, please refer to Chapter 3 on Configuring Query Store |
current_storage_size_mb | bigint | Tells you the current size of Query Store is used in MBs |
flush_interval_seconds | bigint | Tells you how often Query Store is persisting data to disk. The default is 900 seconds (15 minutes) |
interval_length_minutes | bigint | Tells Query Store what intervals to roll up the statistics into. The following are valid values: 1, 5, 10, 15, 30, 60, and 1440 minutes. The default value is 60 minutes |
max_storage_size_mb | bigint | Tells Query Store the maximum amount of disk space that it can use |
stale_query_threshold_days | bigint | The number of days a query stays in Query Store. The default value is 30 days. If you set the value to 0, it will disable the retention policy. For Azure SQL Database Basic Edition, the default value is 7 days |
max_plans_per_query | bigint | The maximum number of plans Query Store will keep for each query. Once the maximum number is reached, Query Store no longer captures plans for that query. If you set the value to 0, it will have no limitation Applies to SQL Server 2017 and up |
query_capture_mode | smallint | The capture mode for Query Store. Valid values are as follows: 1 = ALL – captures all queries. (Default for SQL Server 2016 and up) 2 = AUTO – captures queries based on the usage patterns. (Default for Azure SQL Database) 3 = NONE – tells Query Store to stop capturing new queries. Query Store however continues to collect statistics for queries already in Query Store 4 = CUSTOM – tells Query Store to use custom configurations options to determine what queries to store (applies to SQL Server 2019 only) |
Column name | Data type | Description |
query_capture_mode_desc | nvarchar(60) | Description of the capture mode of Query Store. Valid values are as follows: ALL AUTO CUSTOM (applies to SQL Server 2019 only) NONE |
capture_policy_execution_count | int | Number of times a query executes while query capture mode is CUSTOM before it is captured (applies to SQL Server 2019 only) |
capture_policy_total_compile_cpu_time_ms | bigint | Total CPU compile time in ms before is captured while query capture mode is CUSTOM (applies to SQL Server 2019 only) |
capture_policy_total_execution_cpu_time_ms | bigint | Total CPU execution time in ms before is captured while query capture mode is CUSTOM (applies to SQL Server 2019 only) |
capture_policy_state_threshold_hours | int | The amount of time queries will collect data while query capture mode is CUSTOM to determine if the query stats should be captured (applies to SQL Server 2019 only) |
size_based_cleanup_mode | smallint | Tells Query Store rather or not to clean up Query Store when it gets close to the maximum size. Valid values are as follows: 0 = OFF – do not clean up automatically 1 = AUTO – clean up automatically when 90% of the maximum size is reached. This is the default value. The least expensive and oldest queries are removed first until it reaches about 80% free space |
size_based_cleanup_mode_desc | nvarchar(60) | Description of size-based cleanup. Valid values are as follows: OFF AUTO |
wait_stats_capture_mode | smallint | Tells Query Store to capture wait statistics or not. Valid values are as follows: 0 = OFF 1 = ON Applies to SQL Server 2017 and up |
wait_stats_capture_mode_desc | nvarchar(60) | Description of rather wait statistics are captured or not. Valid values are as follows: OFF ON (default) Applies to SQL Server 2017 and up |
actual_state_additional_info | nvarchar(8000) | Additional information on how Query Store ended up in the current state. Usually populated when in the state is not in the expected state |
sys.query_context_settings
Query to retrieve a plan handle from cache
Retrieve set options for a plan handle
Function to retrieve SET options for queries executed
Query to return SET options for a query executed
Column listing and descriptions for sys.query_context_settings
Column name | Data type | Description |
---|---|---|
context_settings_id | bigint | Primary key. This value is in the Showplan XML queries for reference |
set_options | varbinary(8) | Bit mask reflecting SET options. Values for bit mask values are calculated using the following: 1 – ANSI_PADDING 2 – Parallel Plan 4 – FORCEPLAN 8 – CONCAT_NULL_YIELDS_NULL 16 – ANSI_WARNINGS 32 – ANSI_NULLS 64 – QUOTED_IDENTIFIER 128 – ANSI_NULL_DFLT_ON 256 – ANSI_NULL_DFLT_OFF 512 – NoBrowseTable 1024 – TriggerOneRow 2048 – ResyncQuery 4096 – ARITH_ABORT 8192 – NUMERIC_ROUNDABORT 16384 – DATEFIRST 32768 – DATEFORMAT 65536 – LanguageID 131072 – UPON 262144 – ROWCOUNT |
language_id | smallint | The ID of the language. Look for more information online by looking at the sys.languages table |
date_format | smallint | The date format. See the SET DATEFORMAT command for more information |
date_first | smallint | The date first value. See the SET DATEFIRST command for more information |
status | varbinary(2) | Bit mask field indicating the type of query or context in which it was executed. Value can be any combination of flags in hexadecimal: 0x0 – regular query (no specific flags) 0x1 – query that was executed through on or the cursor APIs’ stored procedures 0x2 – query for notification 0x4 – internal query 0x8 – auto parameterized query without universal parameterization 0x10 – cursor fetch refresh query 0x20 – a query that is being used in cursor update requests 0x40 – initial result set is returned when a cursor is opened (Cursor Auto Fetch) 0x80 – encrypted query 0x100 – query in context of row-level security predicate |
required_cursor_options | int | Options specified for cursor |
acceptable_cursor_options | int | Options SQL Server may implicitly convert a cursor to support the execution of the statement |
merge_action_type | smallint | A MERGE statement trigger execution plan is used. Valid values are as follows: 0 – no trigger or executes as a DELETE action. 1 – INSERT plan. 2 – UPDATE plan. 3 – DELETE plan with the addition of an INSERT or UPDATE action |
default_schema_id | int | ID of the default schema, which is used to resolve names that are not fully qualified names |
is_replication_specific | bit | Used for replication |
is_contained | varbinary(1) | 1 indicates a contained database |
sys.query_store_plan
Column listing and descriptions for sys.query_store_plan
Column Name | Data Type | Description |
---|---|---|
plan_id | bigint | Primary key |
query_id | bigint | Foreign key to sys.query_store_query. Refer to Table 5-4 |
plan_group_id | bigint | ID of plan group. Multiple plans are often created for cursor queries. The same group will contain the populate and fetch plans |
engine_version | nvarchar(32) | Version of SQL Server for the compiled plan “major.minor.build.revision” format |
compatibility_level | smallint | Compatibility level of the database when the query was ran |
query_plan_hash | varbinary(8) | MD5 hash of the query plan |
query_plan | nvarchar(max) | Showplan XML for the query plan |
is_online_index_plan | bit | Indicates rather the plan was used during an online index rebuild |
is_trivial_plan | bit | The plan is a trivial plan |
is_parallel_plan | bit | The plan is parallel plan |
is_forced_plan | bit | The plan is a forced plan marked by using stored procedure sys.sp_query_store_forced_plan. Plan forcing does not guarantee this exact plan will be used. The query compiles and compares the new compiled plan to the one that was forced. If it fails to match, the column force_failure_count increments and the column last_force_failure_reason will record the reason |
is_natively_compiled | bit | The plan includes natively compiled memory-optimized procedure. Valid values are as follows: 0 – false 1 – true |
force_failure_count | bigint | The number of times that forcing this plan has failed |
last_force_failure_reason | int | Last reason why plan forcing failed. Valid values are as follows: 0 – no failure 8637 – ONLINE_INDEX_BUILD 8683 – INVALID_STARJOIN 8684 – TIME_OUT 8689 – NO_DB 8690 – HINT_CONFLICT 8691 – SETOPT_CONFLICT 8694 – DQ_NO_FORCING_SUPPORTED 8698 – NO_PLAN 8712 – NO_INDEX 8713 – VIEW_COMPILE_FAILED <other value> – GENERAL_FAILURE |
last_force_failure_reason_desc | nvarchar(128) | Description of last force plan failure. Valid values are as follows: ONLINE_INDEX_BUILD – online rebuild was occurring on the a table in the query while the query was trying to modify data INVALID_STARJOIN – contains invalid StarJoin TIME_OUT – forced plan could not be found by the optimizer in the number of allowed operations HINT_CONFLICT – query hint conflicts prevented the query from being compiled DQ_NO_FORCING_SUPPORTED – plan conflicts with the use of a distributed query or full-text operations NO_PLAN – forced plan could not be verified so the query processor did not produce a query plan NO_INDEX – index specified in the plan no longer exists VIEW_COMPILE_FAILED – problem exists in an indexed view referenced in the plan GENERAL_FAILURE – general forcing error |
count_compiles | bigint | The number of compiles |
initial_compile_start_time | datetimeoffset | Initial compile start time |
last_compile_start_time | datetimeoffset | Last compile start time |
last_execution_time | datetimeoffset | Last execution time |
avg_compile_duration | float | Average compile duration |
last_compiled_duration | bigint | Last compile duration |
plan_forcing_type | int | Plan forcing type 0 – NONE 1 – MANUAL 2 – AUTO |
plan_forcing_type_desc | nvarchar(60) | Text description of plan_forcing_type NONE – no plan forcing MANUAL – plan forced by user AUTO – plan forced by automatic tuning |
Note
When querying using the datetimeoffset datatypes, you need to specify the time zone the data was generated in to see the data at the times it was generated because all data is stored in UTC time in this datatype.
sys.query_store_query
Column listing and descriptions for sys.query_store_query
Column name | Data type | Description |
---|---|---|
query_id | bigint | Primary key |
query_text_id | bigint | Foreign key to sys.query_store_query_text. Refer to Table 5-5 |
context_settings_id | bigint | Foreign key to sys.query_context_settings. Refer to Table 5-2 |
object_id | bigint | ID of the database object. Value will be 0 for ad-hoc queries |
batch_sql_handle | varbinary(64) | ID of the statement batch the query is part of. Populated only if the query references temporary tables or table variables |
query_hash | binary(8) | MD5 hash of the query including optimizer hints |
is_internal_query | bit | The query was generated internally |
query_paramterization_type | tinyint | Kind of parameterization: 0 – none 1 – user 2 – simple 3 – forced |
query_paramterization_type_desc | nvarchar(60) | Text description of type of parameterization |
initial_compile_start_time | datetimeoffset | Initial compile start time |
last_compile_start_time | datetimeoffset | Last compile start time |
last_execution_time | datetimeoffset | Last execution time |
last_compile_batch_sql_handle | varbinary(64) | Last SQL batch handle for this query. It can be used with sys.dm_exec_sql_text to get the full text of the batch |
last_compile_batch_offset_start | bigint | Last start compile batch line number |
last_compile_batch_offset_end | bigint | Last end compile batch line number |
count_compiles | bigint | The number of compiles |
avg_compile_duration | float | Average compile time in microseconds |
last_compile_duration | bigint | Last compile time in microseconds |
avg_bind_duration | float | Average bind time in microseconds |
last_bind_duration | bigint | Last bind time in microseconds |
avg_bind_cpu_time | float | Average bind CPU time in microseconds |
last_bind_cpu_time | bigint | Last bind CPU time in microseconds |
avg_optimize_duration | float | Average optimize time in microseconds |
last_optimize_duration | bigint | Last optimize time in microseconds |
avg_optimize_cpu_time | float | Average optimize CPU time in microseconds |
last_optimize_cpu_time | bigint | Last optimize CPU time in microseconds |
avg_compile_memory_kb | float | Average compile memory in kilobytes |
last_compile_memory_kb | bigint | Last compile memory in kilobytes |
max_compile_memory_kb | bigint | Max compile memory in kilobytes |
is_clouddb_internal_query | bit | Always 0 on on-premises instances |
sys.query_store_query_text
Retrieve statements for an object
Column listing and descriptions for sys.query_store_query_text
Column name | Data type | Description |
---|---|---|
query_text_id | bigint | Primary key |
query_sql_text | nvarchar(max) | SQL text of the query as provided by user |
statement_sql_handle | varbinary(64) | SQL handle of the query |
is_part_of_encrypted_module | bit | Indicates if the query is part of an encrypted module |
has_restricted_text | bit | Indicates if the query text contains passwords or other unmentionable words |
sys.query_store_wait_stats
Wait stats for statements in an object
Column listing and descriptions for sys.query_store_wait_stats
Column name | Data type | Description |
---|---|---|
wait_stats_id | bigint | Identifier of the row representing wait statistics for the plan_id, runtime_stats_interval_id, execution_type, and wait_category. It is unique only for the past runtime statistics intervals. For currently active intervals, there may be multiple rows representing one row of statistics flushed to disk and possible multiple rows for data still held in memory |
plan_id | bigint | Foreign key to sys.query_store_plan. Refer to Table 5-3 |
runtime_stats_interval_id | bigint | Foreign key to sys.query_store_runtime_stats_interval. Refer to Table 5-8 |
wait_category | tinyint | Represents what category wait time is aggregated into. Refer to Table 5-7 for wait statistics type mappings and valid values |
wait_category_desc | nvarchar(128) | Description of the wait statistics category. Refer to Table 5-7 for wait statistics type mappings and valid values |
execution_type | tinyint | Determines the type of query execution. Valid values are as follows: 0 – regular execution: successful 3 – client aborted the execution 4 – exception aborted execution |
execution_type_desc | nvarchar(128) | Textual description of the execution type field. Valid values are as follows: 0 – regular 3 – aborted 4 – exception |
total_query_wait_time_ms | bigint | Total CPU wait time in the aggregation interval in milliseconds |
avg_query_wait_time_ms | float | Average CPU wait duration per execution in the aggregation interval in milliseconds |
last_query_wait_time_ms | bigint | Last CPU wait duration in the aggregation interval in milliseconds |
min_query_wait_time_ms | bigint | Minimum CPU wait time in the aggregation interval in milliseconds |
max_query_wait_time_ms | bigint | Maximum CPU wait time in the aggregation interval in milliseconds |
stdev_query_wait_time_ms | float | Standard deviation CPU wait time in the aggregation interval in milliseconds |
Wait statistics categories mapping table
Wait statistics ID | Wait statistics category description | Wait statistics types included in the category |
---|---|---|
0 | Unknown | Unknown |
1 | CPU | SOS_SCHEDULER_YIELD |
2 | Worker thread | THREADPOOL |
3 | Lock | LCK_M_% |
4 | Latch | LATCH_% |
5 | Buffer latch | PAGELATCH_% |
6 | Buffer IO | PAGEIOLATCH_% |
7 | Compilation* | RESOURCE_SEMAPHORE_QUERY_COMPILE |
8 | SQL CLR | CLR%, SQLCLR% |
9 | Mirroring | DBMIRROR% |
10 | Transaction | XACT%, DTC%, TRAN_MARKLATCH_%, MSQL_XACT_%, TRANSACTION_MUTEX |
11 | Idle | SLEEP_%, LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES, FT_IFTS_SCHEDULER_IDLE_WAIT, XE_DISPATCHER_WAIT, REQUEST_FOR_DEADLOCK_SEARCH, LOGMGR_QUEUE, ONDEMAND_TASK_QUEUE, CHECKPOINT_QUEUE, XE_TIMER_EVENT |
12 | Preemptive | PREEMPTIVE_% |
13 | Service broker | BROKER_% (but not BROKER_RECEIVE_WAITFOR) |
14 | Tran Log IO | LOGMGR, LOGBUFFER, LOGMGR_RESERVE_APPEND, LOGMGR_FLUSH, LOGMGR_PMM_LOG, CHKPT, WRITELOGF |
15 | Network IO | ASYNC_NETWORK_IO, NET_WAITFOR_PACKET, PROXY_NETWORK_IO, EXTERNAL_SCRIPT_NETWORK_IOF |
16 | Parallelism | CXPACKET, EXCHANGE |
17 | Memory | RESOURCE_SEMAPHORE, CMEMTHREAD, CMEMPARTITIONED, EE_PMOLOCK, MEMORY_ALLOCATION_EXT, RESERVED_MEMORY_ALLOCATION_EXT, MEMORY_GRANT_UPDATE |
18 | User wait | WAITFOR, WAIT_FOR_RESULTS, BROKER_RECEIVE_WAITFOR |
19 | Tracing | TRACEWRITE, SQLTRACE_LOCK, SQLTRACE_FILE_BUFFER, SQLTRACE_FILE_WRITE_IO_COMPLETION, SQLTRACE_FILE_READ_IO_COMPLETION, SQLTRACE_PENDING_BUFFER_WRITERS, SQLTRACE_SHUTDOWN, QUERY_TRACEOUT, TRACE_EVTNOTIFF |
20 | Full text search | FT_RESTART_CRAWL, FULLTEXT GATHERER, MSSEARCH, FT_METADATA_MUTEX, FT_IFTSHC_MUTEX, FT_IFTSISM_MUTEX, FT_IFTS_RWLOCK, FT_COMPROWSET_RWLOCK, FT_MASTER_MERGE, FT_PROPERTYLIST_CACHE, FT_MASTER_MERGE_COORDINATOR, PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC |
21 | Other disk IO | ASYNC_IO_COMPLETION, IO_COMPLETION, BACKUPIO, WRITE_COMPLETION, IO_QUEUE_LIMIT, IO_RETRY |
22 | Replication | SE_REPL_%, REPL_%, HADR_% (but not HADR_THROTTLE_LOG_RATE_GOVERNOR), PWAIT_HADR_%, REPLICA_WRITES, FCB_REPLICA_WRITE, FCB_REPLICA_READ, PWAIT_HADRSIM |
23 | Log rate governor | LOG_RATE_GOVERNOR, POOL_LOG_RATE_GOVERNOR, HADR_THROTTLE_LOG_RATE_GOVERNOR, INSTANCE_LOG_RATE_GOVERNOR |
sys.query_store_runtime_stats
Top 10 queries by CPU in the last hour
Column listing and descriptions for sys.query_store_runtime_stats
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 intervals, there may be multiple rows representing one row of statistics flushed to disk and possible multiple rows for data still held in memory |
plan_id | Bigint | Foreign key to sys.query_store_plan refers to Table 5-3 |
runtime_stats_interval_id | Bigint | Foreign key to sys.query_store_runtime_stats_interval refers to Table 5-9 |
execution_type | tinyint | Determines the type of query execution. Valid values are as follows: 0 – regular execution: successful 3 – client aborted the execution 4 – exception aborted execution |
execution_type_desc | nvarchar(128) | Textual description of the execution type field. Valid values are as follows: 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 | The average duration for the query plan within the aggregation interval in microseconds |
last_duration | bigint | The last duration for the query plan within the aggregation interval in microseconds |
min_duration | bigint | The minimum duration for the query plan within the aggregation interval in microseconds |
max_duration | bigint | The maximum duration for the query plan within the aggregation interval in microseconds |
stdev_duration | float | The standard deviation duration for the query plan within the aggregation interval in microseconds |
avg_cpu_time | float | The average CPU time for the query plan within the aggregation interval in microseconds |
last_cpu_time | bigint | The last CPU time for the query plan within the aggregation interval in microseconds |
min_cpu_time | bigint | The minimum CPU time for the query plan within the aggregation interval in microseconds |
max_cpu_time | bigint | The maximum CPU time for the query plan within the aggregation interval in microseconds |
stdev_cpu_time | float | The standard deviation CPU time for the query plan within the aggregation interval in microseconds |
avg_logical_io_reads | float | The average number of logical IO reads for the query plan within the aggregation interval in 8KB pages |
last_logical_io_reads | bigint | The last number of logical IO reads for the query plan within the aggregation interval in 8KB pages |
min_logical_io_reads | bigint | The minimum number of logical IO reads for the query plan within the aggregation interval in 8KB pages |
max_logical_io_reads | bigint | The maximum number of logical IO reads for the query plan within the aggregation interval in 8KB pages |
stdev_logical_io_reads | float | The standard deviation number of logical IO reads for the query plan within the aggregation interval in 8KB pages |
avg_logical_io_writes | float | The average number of logical IO writes for the query plan within the aggregation interval |
last_logical_io_writes | bigint | The last number of logical IO writes for the query plan within the aggregation interval |
min_logical_io_writes | bigint | The minimum number of logical IO writes for the query plan within the aggregation interval |
max_logical_io_writes | bigint | The maximum number of logical IO writes for the query plan within the aggregation interval |
stdev_logical_io_writes | float | The standard deviation number of logical IO writes for the query plan within the aggregation interval |
avg_physical_io_reads | float | The average number of physical IO reads for the query plan within the aggregation interval in 8KB pages |
last_physical_io_reads | bigint | The last number of physical IO reads for the query plan within the aggregation interval in 8KB pages |
min_physical_io_reads | bigint | The minimum number of physical IO reads for the query plan within the aggregation interval in 8KB pages |
max_physical_io_reads | bigint | The maximum number of physical IO reads for the query plan within the aggregation interval in 8KB pages |
stdev_physical_io_reads | float | The standard deviation number of physical IO reads for the query plan within the aggregation interval in 8KB pages |
avg_clr_time | float | The average CLR time for the query plan within the aggregation interval in microseconds |
last_clr_time | bigint | The last CLR time for the query plan within the aggregation interval in microseconds |
min_clr_time | bigint | The minimum CLR time for the query plan within the aggregation interval in microseconds |
max_clr_time | bigint | The maximum CLR time for the query plan within the aggregation interval in microseconds |
stdev_clr_time | float | The standard deviation CLR time for the query plan within the aggregation interval in microseconds |
avg_dop_time | float | The average DOP (degree of parallelism) for the query plan within the aggregation interval in microseconds |
last_dop_time | bigint | The last DOP for the query plan within the aggregation interval in microseconds |
min_dop_time | bigint | The minimum DOP for the query plan within the aggregation interval in microseconds |
max_dop_time | bigint | The maximum DOP for the query plan within the aggregation interval in microseconds |
stdev_dop_time | float | The standard deviation DOP for the query plan within the aggregation interval in microseconds |
avg_query_max_used_memory | float | The average memory grant for the query plan within the aggregation interval in 8 KB pages. Always 0 for queries using natively compiled memory optimized procedures |
last_query_max_used_memory | bigint | The last memory grant for the query plan within the aggregation interval in 8 KB pages. Always 0 for queries using natively compiled memory optimized procedures |
min_query_max_used_memory | bigint | The minimum memory grant for the query plan within the aggregation interval in 8 KB pages. Always 0 for queries using natively compiled memory optimized procedures |
max_query_max_used_memory | bigint | The maximum memory grant for the query plan within the aggregation interval in 8 KB pages. Always 0 for queries using natively compiled memory optimized procedures |
stdev_query_max_used_memory | float | The standard deviation memory grant for the query plan within the aggregation interval in 8 KB pages. Always 0 for queries using natively compiled memory optimized procedures |
avg_rowcount | float | The average number of returned rows for the query plan within the aggregation interval |
last_rowcount | bigint | The last number of returned rows for the query plan within the aggregation interval |
min_rowcount | bigint | The minimum number of returned rows for the query plan within the aggregation interval |
max_rowcount | bigint | The maximum number of returned rows for the query plan within the aggregation interval |
stdev_rowcount | float | The standard deviation number of returned rows for the query plan within the aggregation interval |
avg_log_bytes_used | float | The 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 | The last number of bytes in the database log used by the query plan within the aggregation interval. Applies only to Azure SQL Database |
min_log_bytes_used | bigint | The 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 | The 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 | The standard deviation number of bytes in the database log used by the query plan within the aggregation interval Applies only to Azure SQL Database |
avg_tempdb_space_used | float | Average amount of tempdb space used by the query |
last_tempdb_space_used | bigint | The last amount of tempdb space used by the query plan within the aggregation interval |
min_tempdb_space_used | bigint | Minimum amount of tempdb space used by the query plan within the aggregation interval |
max_tempdb_space_used | bigint | Maximum amount of tempdb space used by the query plan within the aggregation interval |
stdev_tempdb_space_used | float | The standard deviation of tempdb space used by the query plan within the aggregation interval |
Column listing and descriptions for sys.query_runtime_stats_interval
Column name | Data type | Description |
---|---|---|
runtime_stats_interval_id | bigint | Primary key. |
start_time | datetimeoffset | Start time of the interval. |
end_time | datetimeoffset | End time of the interval. |
comment | nvarchar(32) | Always NULL. |
sys.query_store_runtime_stats_interval
The sys.query_store_runtime_stats_interval catalog view is the most basic and smallest. It contains the start and end time of each runtime interval. Statistics collection intervals are in 1 minute, 5 minutes, 10 minutes, 15 minutes, 30 minutes, 1 hour, and 1-day segments. See more in Chapter 3 on how to configure this setting. Table 5-9 shows all the column names, data types, and descriptions for the columns in the sys.query_store_runtime_stats_interval catalog view.
Conclusion
To get the most out of Query Store, you will need to understand how the catalog views are related to each other and query them directly for different use cases. All the information you need about what is running against your SQL Server is contained here depending on your settings and can be customized to your specifications. The data is here for the harvesting, and we looked at visual ways of looking at this data in Chapter 4 and will look at a stored procedure for retrieving data in Chapter 10.