Query Store

The Query Store in SQL Server, first introduced in SQL Server 2016, streamlines the process of troubleshooting query execution plans. The Query Store, once enabled, automatically captures query execution plans and runtime statistics for your analysis. You can then use the sys.dm_db_tuning_recommendations view to discover where query execution plan regression has occurred and use the stored procedure, sp_query_store_force_plan, to force a specific plan that performs better.

In SQL Server 2019, we now have made some additional enhancements to the default Query Store features. In this section, we will discuss the following topics:

• Changes to default parameter values when enabling Query Store
• A new QUERY_CAPTURE_MODE custom
• Support for fast forward and static cursors

You can configure Query Store with SQL Server Management Studio (SSMS) or with T-SQL statements. SSMS configuration includes turning it on and off by setting the operation mode (off, read-only, or read/write), the Query Store size, and other settings. You can find Query Store parameters in the properties of a database by right-clicking on the database and selecting Query Store:

Figure 1.16: Database properties dialogue window

Changes to default parameter values

Two of the existing parameters have new default values compared to SQL Server 2017. These parameters are MAX_STORAGE_SIZE_MB and QUERY_CAPTURE_MODE. The new default values as of SQL Server 2019 are listed here:

• MAX_STORAGE_SIZE_MB has a default value of 1000 (MB)
• The QUERY_CAPTURE_MODE has a default value of AUTdO

QUERY_CAPTURE_MODE

In previous versions of SQL Server, the default value for the QUERY_CAPTURE_MODE was set to ALL, and therefore all query plans were captured and stored. As mentioned in the previous section, the default value has now been changed to AUTO.

Setting the QUERY_CAPTURE_MODE to AUTO means that no query plans or associated runtime statistics will be captured for the first 29 executions in a single day. Query plans and runtime statistics are not captured until the 30th execution of a plan. This default setting can be changed by using the new custom mode.

QUERY_CAPTURE_MODE: CUSTOM

Before 2019, there were three available values for the query_capture_mode; those values were NONE, ALL, and AUTO. We have now added a fourth option, which is CUSTOM.

The CUSTOM mode provides you with a mechanism for changing the default settings of the Query Store. For example, the following settings can be modified when working in CUSTOM mode:

• EXECUTION_COUNT
• TOTAL_COMPILE_CPU_TIME_MS
• TOTAL_EXECUTION_CPU_TIME_MS
• STALE_CAPTURE_POLICY_THRESHOLD

First, you can verify and validate the current Query Store settings by using the sys.database_query_store_options view:

SELECT actual_state_desc, stale_query_threshold_days, query_capture_mode_desc, 
  capture_policy_execution_count, capture_policy_total_compile_cpu_time_ms, 
  capture_policy_total_execution_cpu_time_ms 
FROM sys.database_query_store_options

The output is as follows:

Figure 1.17: Verifying and validating the Query Store settings

To modify the default settings, you will first change the query capture mode to custom and then apply changes to the default values. Look at the following code by way of an example:

ALTER DATABASE AdventureWorks2017 
SET QUERY_STORE = ON
(
  QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY =
  (
    EXECUTION_COUNT = 20,
    TOTAL_COMPILE_CPU_TIME_MS = 1000,
    TOTAL_EXECUTION_CPU_TIME_MS = 100,
    STALE_CAPTURE_POLICY_THRESHOLD = 7 DAYS
  )
);

The output is as follows:

Figure 1.18: Modifying the default settings

Support for FAST_FORWARD and STATIC Cursors

We have added another exciting update to the Query Store. You can now force query execution plans for fast forward and static cursors. This functionality supports T-SQL and API cursors. Forcing execution plans for fast forward and static cursors is supported through SSMS or T-SQL using sp_query_store_force_plan.