SQL Server 2019 Query Store Defaults

When you look at articles, posts and documents about new features and enhancements in SQL Server 2019 CTP2, you will find nothing about Query Store. However, there are some  graphical enhancements in SQL Server Management Studio in the version 18.0, also default configuration for Query Store attributes is changed too.

First SSMS 18.0. From this version, you can see another Query Store report – Query Wait Statistics. When you click on it, you can see aggregate waits per category in a given time interval (default is last hour). Bl1

By clicking on the wait category bar, a new screen shows up with ids of queries that contribute to this wait category.

Bl2

You can also use tabular view for initial report (aggergated time for wait categories):

Bl3

The second change is related to default configuration of Query Store. When you enable Query Store for a database without specifying attributes, by using the Properties tab or command:

ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON;

SQL Server adds default values for Query Store parameters. In SQL Server 2019 Microsoft decided to change default values for some of them. Here is the table with the attributes and former and current default values:

Attribute SQL Server 2017 SQL Server 2019
max_storage_size_mb 100 500
interval_length_minutes 60 15
query_capture_mode ALL AUTO
flush_interval_seconds 900 3,000
max_plans_per_query 200 1,000

 

Thanks for reading.

Related Articles

Leave a Reply

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