SQL Server 2019 CTP3.0 Query Store Changes

2019-05-29
SQL Server, SQL Server 2019

SQL Server 2019 CTP 3.0 brings small changes in the Query Store. Two parameters have new default values compared to previous SQL Server version:

Attribute SQL Server 2017 SQL Server 2019
MAX_STORAGE_SIZE_MB 100 1000
QUERY_CAPTURE_MODE ALL AUTO

In addition to default value, the minimum number of query executions in the AUTO query_capture_mode for storing the query, its plan(s) and runtime statistics in SQL Server 2019 has been increased from 3 to 30. That means, Query Store does not store anything for first 29 query executions. It reserves query_ids, but it starts storing execution plan and runtime stats from 30th execution in a single day. When you would run the following code, you would find nothing in Query Store:

IF DB_ID('Z') IS NULL
CREATE DATABASE Z;
GO
USE Z;
GO
ALTER DATABASE Z SET QUERY_STORE CLEAR;
GO
ALTER DATABASE Z SET QUERY_STORE = ON;
GO
CREATE TABLE dbo.T(id INT);
GO
SELECT COUNT(*) FROM T;
GO 29
SELECT SUM(id) FROM T;
GO 29

With the next execution of a query, its details, plan and runtime stats will be stored in Query Store:

SELECT SUM(id) FROM T;

Bl1

As you can see, the count_executions attribute shows 1; all previous executions are ignored; only query_id is preserved, but not stored; therefore, our query has id 2 because it was second in the sample code sequence.

There is another enhancement for the query_capture_mode attribute. In SQL Server 2017 it can have the following values: ALL, AUTO and NONE. SQL Server 2019 brings a new one – CUSTOM. SQL Server Management Studio 18 (SSMS) does not support it, but you can configure this feature by using Transact-SQL code. The CUSTOM mode configures QUERY_CAPTURE_POLICY options. The following options can be used to define the minimum condition for a query to be captured by Query Store:

EXECUTION_COUNT – minimal number of query executions for a query to be captured. As we saw earlier, default is 30 execution per day.

TOTAL_COMPILE_CPU_TIME_MS – minimum total query compilation time in a day to be persisted in Query Store. Default is 1000 milliseconds of CPU time.

TOTAL_ EXECUTION_CPU_TIME_MS – minimum total query CPU time spent for query execution per day to be persisted in Query Store. Default is 100 milliseconds.

A query will be captured if at least one of these three conditions is fulfilled; i.e. they are evaluated as EXECUTION_COUNT OR TOTAL_COMPILE_CPU_TIME_MS OR TOTAL_ EXECUTION_CPU_TIME_MS.

The fourth option is STALE_CAPTURE_POLICY_THRESHOLD which defines the evaluation interval period to determine if a query should be captured. The default is 1 day, and it can be set from 1 hour to 7 days.

Let’s try to reconfigure Query Store to capture queries after 20 executions:

USE Z;
GO
ALTER DATABASE Z SET QUERY_STORE CLEAR;
GO
ALTER DATABASE Z SET QUERY_STORE = ON
(
QUERY_CAPTURE_POLICY =
(
EXECUTION_COUNT = 20,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100,
STALE_CAPTURE_POLICY_THRESHOLD = 1 day
)
);
GO
SELECT COUNT(*) FROM T;
GO 29
SELECT SUM(id) FROM T;
GO 29

EDIT: When you execute this code, you see no errors, but Query Store is still empty, so, the CUSTOM mode does not work in SQL Server 2019 CTP3.0 even with Transact-SQL.. The QUERY_CAPTURE_POLICY attribute settings are implemented only if you set the QUERY_CAPTURE_MODE to CUSTOM:

ALTER DATABASE Z 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 = 1 day
)
);
GO

So, my initial  claim is wrong – the CUSTOM mode for the QUERY_CAPTURE_MODE attribute works in SQL Server2019 CTP 3.0, you just need to use proper syntax.

Thanks for reading.

         

Related Articles