--- PDF PAGE 747 ---
742
INSERT INTO dbo.Customers
SELECT * FROM #Customers;
GO
CREATE INDEX idx_LastName ON dbo.Customers(LastName)
GO
Now that we have a database, we can enable Query Store for the database, by using 
the command in Listing 20-2.
Listing 20-2.  Enable Query Store
ALTER DATABASE Chapter20 SET QUERY_STORE = ON
As well as enabling Query Store using an ALTER DATABASE command, this method 
is also used to configure Query Store properties. Table 20-1 details the Query Store–
related SET options that are available.
Table 20-1.  Query Store SET Options
SET Option
Description
ON
Enables the Query Store for the specified database.
OFF
Disables the Query Store for the specified database.
CLEAR
Clears the contents of the Query Store.
OPERATION_MODE
Can be configured as READ_ONLY or the default of READ_
WRITE. When configured as READ_WRITE, data is written to 
the Query Store and can be read from the Query Store. When 
set to READ_ONLY, data can still be read from the Query Store, 
but no further data will be written to it. The Query Store will 
automatically switch to READ_ONLY, if it runs out of space for 
storing new data.
CLEANUP_POLICY
Accepts STALE_QUERY_THRESHOLD_DAYS and a number of 
days. This determines how many days data for each query 
should be retained.
(continued)
Chapter 20  Query Store

--- PDF PAGE 748 ---
743
Table 20-1.  (continued)
SET Option
Description
DATA_FLUSH_INTERVAL_SECONDS Query Store data is flushed to disk asynchronously. DATA_
FLUSH_INTERVAL_SECONDS specifies how frequently data 
should be flushed.
MAX_STORAGE_SIZE_MB
Specifies the maximum amount of space that can be used by 
Query Store data. If this value overflows, the Query Store will 
switch to READ_ONLY operational mode.
INTERVAL_LENGTH_MINUTES
Specifies the time interval at which runtime execution 
statistics data is aggregated.
SIZE_BASED_CLEANUP_MODE
Specifies if an automatic cleanup is triggered, when the Query 
Store reaches 90% of its maximum size threshold. Can be set 
to AUTO or OFF. When set to AUTO, the oldest, least expensive 
queries will be removed, until the size drops to around 80% of 
the Query Store’s maximum size threshold.
QUERY_CAPTURE_MODE
Specifies which queries data should be captured for. Can be 
configured as ALL, NONE, AUTO, or CUSTOM. When set to ALL, 
statistics will be captured for all queries. When set to NONE, 
statistics will only be captured for queries which already exist 
in the Query Store. When configured as AUTO, SQL Server will 
capture statistics for the most expensive queries, based on 
execution count and resource utilization. When configured as 
CUSTOM, then you will have more granular control over which 
queries have statistics captured. When CUSTOM is specified, 
QUERY_CAPTURE_POLICY should also be specified.
MAX_PLANS_PER_QUERY
Specifies the maximum number of plans that will be captured 
for each query, defaulting to 200.
WAIT_STATS_CAPTURE_MODE
Specifies if wait statistics should be captured for queries. This 
is, of course, very useful, but comes at the expense of extra 
disk space consumed.
QUERY_CAPTURE_POLICY
Can be used when QUERY_CAPTURE_MODE is set to 
CUSTOM. Please see Table 20-2 for details of the available 
options that can be passed.
Chapter 20  Query Store

--- PDF PAGE 748 ---
743
Table 20-1.  (continued)
SET Option
Description
DATA_FLUSH_INTERVAL_SECONDS Query Store data is flushed to disk asynchronously. DATA_
FLUSH_INTERVAL_SECONDS specifies how frequently data 
should be flushed.
MAX_STORAGE_SIZE_MB
Specifies the maximum amount of space that can be used by 
Query Store data. If this value overflows, the Query Store will 
switch to READ_ONLY operational mode.
INTERVAL_LENGTH_MINUTES
Specifies the time interval at which runtime execution 
statistics data is aggregated.
SIZE_BASED_CLEANUP_MODE
Specifies if an automatic cleanup is triggered, when the Query 
Store reaches 90% of its maximum size threshold. Can be set 
to AUTO or OFF. When set to AUTO, the oldest, least expensive 
queries will be removed, until the size drops to around 80% of 
the Query Store’s maximum size threshold.
QUERY_CAPTURE_MODE
Specifies which queries data should be captured for. Can be 
configured as ALL, NONE, AUTO, or CUSTOM. When set to ALL, 
statistics will be captured for all queries. When set to NONE, 
statistics will only be captured for queries which already exist 
in the Query Store. When configured as AUTO, SQL Server will 
capture statistics for the most expensive queries, based on 
execution count and resource utilization. When configured as 
CUSTOM, then you will have more granular control over which 
queries have statistics captured. When CUSTOM is specified, 
QUERY_CAPTURE_POLICY should also be specified.
MAX_PLANS_PER_QUERY
Specifies the maximum number of plans that will be captured 
for each query, defaulting to 200.
WAIT_STATS_CAPTURE_MODE
Specifies if wait statistics should be captured for queries. This 
is, of course, very useful, but comes at the expense of extra 
disk space consumed.
QUERY_CAPTURE_POLICY
Can be used when QUERY_CAPTURE_MODE is set to 
CUSTOM. Please see Table 20-2 for details of the available 
options that can be passed.
Chapter 20  Query Store

--- PDF PAGE 749 ---
744
Table 20-2 details the options that can be configured for QUERY_CAPTURE_POLICY.
Table 20-2.  QUERY_CAPTURE_POLICY Options
Option
Description
STALE_CAPTURE_POLICY_THRESHOLD
Specifies an evaluation period, which is used by the 
other parameters, to determine if a query should have its 
statistics captured. Can be specified in hours or days.
EXECUTION_COUNT
Specifies the minimum number of executions within the 
evaluation period that should occur for a query to be 
captured.
TOTAL_COMPILE_CPU_TIME_MS
Specifies the total amount of CPU compilation time 
within the evaluation period that should occur for a 
query to be captured.
TOTAL_EXECUTION_CPU_TIME_MS
Specifies the total amount of CPU execution time within 
the evaluation period that should occur for a query to be 
captured.
Let’s use the command in Listing 20-3 to configure the Query Store for the Chapter20 
database. Here, we are configuring the Query Store to retain data for 30 days, persist data 
to disk every 5 minutes, capture wait statistics for queries, and run an automatic cleanup 
when 90% of a 2GB threshold is reached.
Listing 20-3.  Configure the Query Store
ALTER DATABASE Chapter20
SET QUERY_STORE = ON (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
      DATA_FLUSH_INTERVAL_SECONDS = 300,
      MAX_STORAGE_SIZE_MB = 2048,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      WAIT_STATS_CAPTURE_MODE = ON
) ;
Chapter 20  Query Store

--- PDF PAGE 756 ---
751
Each bar in the bar chart is clickable. For example, if I were to click the CPU bar, then 
the drill-through report in Figure 20-7 would be displayed.
Figure 20-7.  CPU drill-through report
By default, the top half of the grid will show the most expensive queries (labelled by 
Plan ID), based on Total Wait Time, but as you can see, the Based On drop-down list can 
be changed to base the results on average, min, max wait time, or the standard deviation.
The lower half of the report shows the graphical representation of the Query Plan 
that has been selected in the top half of the report.
Query Store T-SQL Objects
As well as graphical reports, SQL Server also exposes a number of catalog views which 
can be used to retrieve Query Store data. Table 20-3 details the catalog views which are 
exposed.
Chapter 20  Query Store

--- PDF PAGE 757 ---
752
Table 20-4 details how each wait type category maps to underlying wait types.
Table 20-3.  Query Store Catalog Views
Catalog View
Description
Query_store_plan
Stores information about every query plan that is associated with a 
query.
Query_store_query
Stores query information and aggregated runtime statistics.
Query_store_wait_stats
Stores wait statistic details. Please see Table 20-4 for mappings of 
each wait type.
Query_store_query_text
Stores the SQL handle and SQL text of each query.
Query_store_runtime_stats
Stores the runtime statistics for each query.
Table 20-4.  Wait Statistics Mappings
Wait Category
Wait Types
CPU
SOS_SCHEDULER_YIELD
Worker Thread
THREADPOOL
Lock
LCK_M_*
Latch
LATCH_*
Buffer Latch
PAGELATCH_*
Buffer IO
PAGEIOLATCH_*
SQL CLR
CLR*
SQLCLR*
Mirroring
DBMIRROR*
Transaction
ACT*
DTC*
TRAN_MARKLATCH_*
MSQL_XACT_*
TRANSACTION_MUTEX
(continued)
Chapter 20  Query Store

--- PDF PAGE 757 ---
752
Table 20-4 details how each wait type category maps to underlying wait types.
Table 20-3.  Query Store Catalog Views
Catalog View
Description
Query_store_plan
Stores information about every query plan that is associated with a 
query.
Query_store_query
Stores query information and aggregated runtime statistics.
Query_store_wait_stats
Stores wait statistic details. Please see Table 20-4 for mappings of 
each wait type.
Query_store_query_text
Stores the SQL handle and SQL text of each query.
Query_store_runtime_stats
Stores the runtime statistics for each query.
Table 20-4.  Wait Statistics Mappings
Wait Category
Wait Types
CPU
SOS_SCHEDULER_YIELD
Worker Thread
THREADPOOL
Lock
LCK_M_*
Latch
LATCH_*
Buffer Latch
PAGELATCH_*
Buffer IO
PAGEIOLATCH_*
SQL CLR
CLR*
SQLCLR*
Mirroring
DBMIRROR*
Transaction
ACT*
DTC*
TRAN_MARKLATCH_*
MSQL_XACT_*
TRANSACTION_MUTEX
(continued)
Chapter 20  Query Store

--- PDF PAGE 758 ---
753
Table 20-4.  (continued)
Wait Category
Wait Types
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
Preemptive (Preemptive IO)
PREEMPTIVE_*
Service Broker
BROKER_*
Transaction Log IO
LOGMGR
LOGBUFFER
LOGMGR_RESERVE_APPEND
LOGMGR_FLUSH
LOGMGR_PMM_LOG
CHKPT
WRITELOG
Network IO
ASYNC_NETWORK_IO
NET_WAITFOR_PACKET
PROXY_NETWORK_IO
EXTERNAL_SCRIPT_NETWORK_IOF
Parallelism
CXPACKET
EXCHANGE
(continued)
Chapter 20  Query Store

--- PDF PAGE 759 ---
754
Table 20-4.  (continued)
Wait Category
Wait Types
Memory
RESOURCE_SEMAPHORE, CMEMTHREAD
CMEMPARTITIONED, EE_PMOLOCK
MEMORY_ALLOCATION_EXT
RESERVED_MEMORY_ALLOCATION_EXT
MEMORY_GRANT_UPDATE
User Wait
WAITFOR
WAIT_FOR_RESULTS
BROKER_RECEIVE_WAITFOR
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
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
(continued)
Chapter 20  Query Store

--- PDF PAGE 760 ---
755
Wait Category
Wait Types
Other IO
ASYNC_IO_COMPLETION, IO_COMPLETION
BACKUPIO, WRITE_COMPLETION
IO_QUEUE_LIMIT
IO_RETRY
Replication
SE_REPL_*
REPL_*, HADR_*
PWAIT_HADR_*, REPLICA_WRITES
FCB_REPLICA_WRITE, FCB_REPLICA_READ
PWAIT_HADRSIM
Log Rate Governor
LOG_RATE_GOVERNOR
POOL_LOG_RATE_GOVERNOR
HADR_THROTTLE_LOG_RATE_GOVERNOR
INSTANCE_LOG_RATE_GOVERNOR
* Denotes a wildcard, where all waits that match the portion to the left of the * are included
Table 20-4.  (continued)
Tip  Idle and User Wait are unlike other Wait Type categories, in the respect that 
they are not waiting for a resource, they are waiting for work to do.
For example, the query in Listing 20-5 will return the three highest wait categories, by 
total wait time, excluding idle and user waits, which are “healthy” waits.
Listing 20-5.  Return Top 3 Wait Types
SELECT TOP 3
          wait_category_desc
        , SUM(total_query_wait_time_ms) TotalWaitTime
FROM sys.query_store_wait_stats
WHERE wait_category_desc NOT IN ('Idle', 'User Wait')
GROUP BY wait_category_desc
ORDER BY SUM(total_query_wait_time_ms) DESC
Chapter 20  Query Store

--- PDF PAGE 762 ---
757
As well as using the GUI to impart performance improvements on queries, there are 
also a number of system stored procedures exposed. These procedures are detailed in 
Table 20-5 and can be used to manage both plans and the Query Store itself.
Table 20-5.  Query Store Stored Procedures
Procedure
Description
Sp_query_store_flush_db
Flushes Query Store data to disk
Sp_query_store_force_plan
Forces a query to use a specific plan
Sp_query_store_unforce_plan
Removes a forced plan from a query
Sp_query_store_reset_exec_stats
Clears the runtime statistics from the Query Store, for a 
specific query
Sp_query_store_remove_plan
Removes a specific plan from the Query Store
Sp_query_store_remove_query
Removes a query and all associated information from the 
Query Store
For example, to Unforce the plan that we forced upon our query in the previous 
example, we could use the query in Listing 20-6.
Listing 20-6.  Unforce a Query Plan
EXEC sp_query_store_unforce_plan @query_id=109, @plan_id=168
Summary
The Query Store is a very powerful feature of SQL Server, which allows DBAs to monitor 
the performance of specific queries and their plans. Because the data is flushed to disk, 
this means that the data persists, even after an instance is restarted.
Six standard reports are available, which allow DBAs to view information such as 
regressed queries, the most resource-intensive queries, or even wait statistics, by query.
Chapter 20  Query Store