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.
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.
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.
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.
ALTER DATABASE ContosoRetailDW SET QUERY_STORE = ON;
Any or all of the configuration settings can be applied in a single ALTER DATABASE statement.
ALTER DATABASE ContosoRetailDW
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
(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
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.
ALTER DATABASE ContosoRetailDW
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15)
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.
SELECT desired_state, actual_state, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options
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.
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.
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.