SQL Server 2016: Query Store Configuration

Introduction

Some of the new features of SQL Server 2016, most notably the Advanced Analytics and Polybase, are unprecedented in earlier versions. In contrast, the Query Store added to SQL Server 2016 doesn’t so much add new features as make the time-honored tasks of query execution monitoring faster, easier, and more convenient. Once turned on for a database, Query Store saves the query plan and execution statistics for queries and enables administrators to determine which queries are placing the greatest demands on system resources. An especially interesting feature is the ease with which Query Store monitors queries that have “regressed”, in other words, queries that are taking longer or requiring more resources than they have in the past.

Enabling Query Store in the Management Studio Graphical Interface

A curious break with the past is seen in the Management Studio. In the past, it has not been uncommon for new features to be available only through T-SQL and only later does a nice graphical interface appear. Query Store appears simultaneously with a slick user interface. Of course, Query Store can be enabled for a database using T-SQL, but it can also be enabled using the database properties dialog in the Management Studio. Click on the “Query Store” page and simply set the Operation Mode (Requested) property to “Read Write.” We shall discuss later why the actual mode may not be the same as the requested mode.

After clicking OK, you can refresh the folder for the newly enabled database and see the new Query Store folder.

Query Store folders in the Object Explorer

A quick and easy graphical interface can also have a negative effect; it can give people the impression that the feature is a “set-and-forget” tool that can be turned-on, forgotten about, and then consulted when desired. Nothing could be further from the truth. To start with, the graphical Regressed Queries tool seems to compare queries executed in the past hour with executions of the same query in the past. After an hour has gone by, a query is itself part of the past rather than something that can be compared with the past. Very likely, T-SQL queries will prove superior to the graphical tool for actual analyses.

Configuring Query Store with T-SQL

Query Store enabling and configuration in T-SQL, is accomplished using the ALTER DATABASE statement. Setting or changing these options does not require exclusive use of the database.

Enabling Query Store

ALTERDATABASE ContosoRetailDW SETQUERY_STORE=ON;

Configuration Settings

Any or all of the configuration settings can be applied in a single ALTER DATABASE statement.

ALTERDATABASE ContosoRetailDW

SETQUERY_STORE (

OPERATION_MODE =READ_WRITE,

CLEANUP_POLICY =

(STALE_QUERY_THRESHOLD_DAYS = 30),

DATA_FLUSH_INTERVAL_SECONDS = 3000,

MAX_STORAGE_SIZE_MB = 500,

INTERVAL_LENGTH_MINUTES = 15,

SIZE_BASED_CLEANUP_MODE =AUTO,

QUERY_CAPTURE_MODE =AUTO,

MAX_PLANS_PER_QUERY = 1000

);

Configuring the Aggregation Interval

By default, Query Store aggregates query execution information in 60-minute intervals. You can change this setting, but not all values are allowed. The permitted settings seem to be: 1, 5, 10, 15, 30, 60, and 1440 minutes.

ALTERDATABASE ContosoRetailDW

SETQUERY_STORE (INTERVAL_LENGTH_MINUTES = 15)

Why Is My “Actual Mode” Read and not Read Write?

Unlike some other SQL Server performance information, such as index usage, Query Store information is persisted in tables in the Primary file group of your database. By default, the space for this storage is limited to a maximum of 100 MB, as can be seen in the properties dialog. If you reach this limit and are not aware of it, the information already saved remains and can be queried, but no new information can be added. Query Store has shifted to Read mode. It is up to the administrator to increase the available space, or delete uninteresting queries, or perhaps clear the store entirely. The amount of hard drive space currently used and the configured maximum can both be examined using sys.database_query_store_options.

A Little Trap

You cannot use Query Store for the master database. This creates a little trap. If you run a query against a query store view while connected to the master database, you will not get an error, but you will not get any rows either. Make sure you are connected to the right database before querying system information about Query Store.

Query Store Information

Not surprisingly, there are new system views and stored procedures that permit you to draw upon the information acquired and saved by the Query Store and to manage the operation of Query Store. It is, unfortunately, also not surprising that using this information can involve some complex SELECT statements to present the information in a form that we humans can readily understand.

System Views

sys.query_store_plan

ys.query_store_query

sys.query_store_query_text

sys.database_query_store_options

sys.query_context_settings

sys.query_store_runtime_stats

sys.query_store_runtime_stats_interval

Stored Procedures

sp_query_store_reset_exec_stats

sp_query_store_remove_plan

sp_query_store_flush_db

sp_query_store_force_plan

sp_query_store_unforce_plan

sp_query_store_remove_plan

sp_query_store_remove_query

Conclusion

Query Store is an easy and convenient addition to the toolbox for administrators who wish to keep track of efficient query execution. In the next installment, we’ll see how to examine and analyze query execution information using Query Store.