SQL Server 2019 CTP3.0 Query Store Changes

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

About author View all posts

Milos R.

1 CommentLeave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *