© Tracy Boggiano and Grant Fritchey 2019
T. Boggiano, G. FritcheyQuery Store for SQL Server 2019https://doi.org/10.1007/978-1-4842-5004-4_5

5. Query Store Catalog Views

Tracy Boggiano1  and Grant Fritchey2
(1)
Cary, NC, USA
(2)
Grafton, MA, USA
 

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

The catalog view that holds the options that tell you the settings for the setup of Query Store is sys.database_query_store_options. This catalog view returns all the options available that are set for the configuration of Query Store. This catalog view has no relationships with any of the other catalog views. Sometimes you need to check the configuration of Query Store across all your databases where Query Store is turned on; you can use the query in Listing 5-1 to return a result set for each database that has Query Store enabled with all the settings.
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL += REPLACE(REPLACE('
      USE [{{DBName}}];
      SELECT "{{DBName}}",
            *
      FROM sys.database_query_store_options; '
      ,'{{DBName}}', [name])
      ,' " ','' '')
FROM sys.databases
WHERE is_query_store_on = 1;
EXEC (@SQL);
Listing 5-1

Check Query Store options across all databases on a SQL Server instance

Table 5-1 displays all the column names, data types, and descriptions for the columns in the sys.database_query_store_options catalog view. Important columns to keep an eye on are the actual_state_desc; you want to make sure it stays in READ_WRITE if the desired_state is READ_WRITE and did not switch to READ_ONLY due to running out of space or ERROR. To understand these options for these columns better, please refer to Chapter 3.
Table 5-1

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

The sys.query_context_settings catalog view holds the context settings that the queries in Query Store were running with such ANSI_NULLS or QUOTED_IDENTIFIER. If different context settings are used with the same query, you will end up with different plans in Query Store. A bit mask is stored in the set_options field to tell us which options were used when a query was run. There are two ways to view the set options for a query. The first is to use the Dynamic Management Function sys.dm_exec_plan_atttibutes and pass in the plan_handle. This method only works if the plan is still in the plan cache. In Listing 5-2 you can retrieve a plan_handle or context_settings_id for a particular query by narrowing down your selection by feeling part of the query in the WHERE clause in the <value> placeholder.
SELECT
      q.query_id,
      qt.query_sql_text,
      qs.plan_handle,
      q.context_settings_id
FROM sys.query_store_query q
      INNER JOIN sys.dm_exec_query_stats qs
            ON q.last_compile_batch_sql_handle =
                  qs.sql_handle
      INNER JOIN sys.query_store_query_text qt
            ON q.query_text_id = qt.query_text_id
      INNER JOIN sys.query_context_settings cs
            ON cs.context_settings_id = q.context_settings_id
WHERE qt.query_sql_text LIKE '%<value>%'
ORDER BY q.query_id
Listing 5-2

Query to retrieve a plan handle from cache

Next, you copy the plan handle for the query you want to see the set options for and put in Listing 5-3 replacing <plan_handle> in the listing.
SELECT *
FROM sys.dm_exec_plan_attributes(<plan_handle>)
WHERE attribute = 'set_options'
Listing 5-3

Retrieve set options for a plan handle

Second, you can create a function then you can query a record from the catalog view and use the context_settings_id returned in Listing 5-2 as the @SetOptions value to view the set options that were used as seen in Listing 5-4.
CREATE FUNCTION fn_QueryStoreSetOptions (@SetOptions as int)
RETURNS VARCHAR(MAX)
AS
BEGIN
      DECLARE @Result VARCHAR(MAX)=",
            @SetOptionFound INT
      DECLARE @SetOptionsList TABLE
      (
            [Value] INT,
            [Option] VARCHAR(60)
      )
      INSERT INTO @SetOptionsList
      VALUES
      (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')
      SELECT TOP 1 @SetOptionFound = ISNULL([Value], -1),
            @Result = ISNULL([Option] , '') + '; '
      FROM @SetOptionsList
      WHERE [Value] <= @SetOptions
      ORDER BY [Value] DESC
      RETURN @Result +
            CASE WHEN @SetOptionFound > -1 THEN
                  dbo.fn_QueryStoreSetOptions(@SetOptions –
                        @SetOptionFound)
                  ELSE ''
            END
END
GO
Listing 5-4

Function to retrieve SET options for queries executed

Next, we need to query the sys.query_context_settings catalog view and CAST the set_options column to an INT value to parse the SET options as seen in Listing 5-5. You can see an example of the output of the results in Figure 5-1.
SELECT dbo.fn_QueryStoreSetOptions(CAST(set_options as int))
FROM sys.query_context_settings
Listing 5-5

Query to return SET options for a query executed

../images/473933_1_En_5_Chapter/473933_1_En_5_Fig1_HTML.jpg
Figure 5-1

Set options query results

Table 5-2 displays all the column names, data types, and descriptions for the columns in the sys.query_context_settings catalog view.
Table 5-2

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

The sys.query_store_plan catalog view stores the query plan associated with each query in Query Store. The most important column in this catalog view is the query_plan column as it shows you the execution plan that was stored for the query. You can copy and paste this into an editor of your choice and save with a .sqlplan extension and open with SSMS to view the graphical execution plan. Two other interesting columns are the engine_version and compatibility_level columns. The engine_version column lets us know what the exact version of SQL Server that was running when the query plan was captured. This is useful for troubleshooting plan regression due to upgrades. The compatibility_level column tells which compatibility level the query was ran under. This is useful when upgrading as well but especially when going from a compatibility level below SQL Server 2014 to SQL Server 2014 and up due to the cardinality estimator (CE) changes made in SQL Server 2014. Table 5-3 displays all the column names, data types, and descriptions for the columns in the sys.query_store_plan catalog view.
Table 5-3

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

All metrics per query executed are stored inside this catalog view. The data in the catalog view is stored at the SQL statement level. Batches are split up into statements in the catalog view allowing for better troubleshooting. Most of the columns in the catalog view are metrics for compiling and binding the plan for the statements. Of note in this catalog view is the object_id column. This column allows you to tie the statements back to their stored procedure, function, or trigger. Table 5-4 shows all the column names, data types, and descriptions for the columns in the sys.query_store_query catalog view .
Table 5-4

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

The sys.query_store_query_text catalog view contains the query text of the SQL Statement to be joined to sys.query_store_query catalog view to get statement level data, not batch level data. Using the object_id from sys.query_store_query, we are in Listing 5-6 querying for all the statements for an object by subbing in an object name in the placeholder <object_name>.
SELECT *
FROM sys.query_store_query_text qt
      INNER JOIN sys.query_store_query q
            ON q.query_text_id = qt.query_text_id
      INNER JOIN sys.objects o on o.object_id = q.object_id
WHERE o.name = '<object_name>'
Listing 5-6

Retrieve statements for an object

Table 5-5 shows all the column names, data types, and descriptions for the columns in the sys.query_store_query_text catalog view.
Table 5-5

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

The sys.query_store_wait_stats catalog view was introduced to Query Store in SQL Server 2017 and gave us major insights into what each query was waiting on when executing. In Listing 5-7 you can see a summary of the wait statistics collected across categories for the statements in an object by subbing in value for <object_name>.
SELECT *
FROM sys.query_store_query_text qt
      INNER JOIN sys.query_store_query q
            ON q.query_text_id = qt.query_text_id
      INNER JOIN sys.objects o on o.object_id = q.object_id
      INNER JOIN sys.query_store_plan p
            ON p.query_id = q.query_id
      INNER JOIN sys.query_store_wait_stats ws
            ON ws.plan_id = p.plan_id
WHERE o.name = '<object_name>'
Listing 5-7

Wait stats for statements in an object

Below in Table 5-6 are all the column names, data types, and descriptions for the columns in the sys.query_store_wait_stats catalog view.
Table 5-6

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 are divided into 23 categories a list of which can be found in Table 5-7 with a list of which wait statistics belong in each category.
Table 5-7

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

* Currently not supported.

sys.query_store_runtime_stats

The sys.query_store_runtime_stats catalog view contains the runtime stats for all the plans that Query Store has store aggregated by the runtime_stats_interval_id. Statistics it collects include average, last, minimum, maximum, and standard deviation for the duration, CPU, logical IO, physical IO, CLR, DOP (degree of parallelism), query max used memory, row count, and for Azure SQL Database log bytes used. Besides the wait statistics catalog view, this catalog view is where you harvest the most data. Listing 5-8 will return the top 10 queries by average duration that have a last_execution_time in the last hour.
SELECT TOP 10 sum(rs.count_executions * rs.avg_duration) avg_duration,
      qt.query_sql_text,
      q.query_id,
      qt.query_text_id,
      p.plan_id,
      rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
      q.query_id,
      qt.query_text_id,
      p.plan_id,
      rs.last_execution_time
ORDER BY avg_duration DESC;
Listing 5-8

Top 10 queries by CPU in the last hour

Table 5-8 shows all the column names, data types, and descriptions for the columns in the sys.query_store_runtime_stats catalog view.
Table 5-8

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

Table 5-9

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.129.249.194