Collecting Query and Plan Information

When a query gets submitted to SQL Server, it runs through a series of processes. These processes ensure that the query is properly written, that the objects in the database referenced by the query actually exist, and, most importantly, that the query is optimized to run quickly. Figure 2-1 shows an overview of this process:

We start with the T-SQL query of course. Only Data Manipulation Language (DML) queries are captured by the Query Store. These are queries that are used to SELECT, UPDATE, DELETE, or INSERT data. Queries that are used to manipulate the data structures, Data Definition Language (DDL), are not captured to the Query Store. When the query first comes to SQL Server, it goes through an internal process called the Algebrizer. This does a number of things, but the two we care about the most are that it validates that the syntax is correct in the parser and it ensures that the objects in the query are actually in the database, through object binding. The Algebrizer also gathers a bunch of information that, along with the query, it passes to the query optimizer.

The query optimizer is the process that determines an efficient way to retrieve or modify your data based on the query you’ve supplied it and the tables, indexes, statistics, and constraints in your database. The result of the query optimization process itself is an execution plan which gets written into a memory space called the plan cache.

With one exception, which we’ll cover in a minute, the Query Store does not interfere with this process in any way. What the Query Store does is capture the output of the processes that create an execution plan. Here’s a graphic showing how the Query Store works side-by-side with the query optimization process as you can see in Figure 2-2:

After the query optimizer does its work and generates an execution plan, an asynchronous process captures the query and the execution plan for the query store. It outputs them to a temporary storage space in memory. On a regular basis, the query information stored in memory gets written to the database through another asynchronous process. Microsoft describes this as occurring with minimal latency. The goal is to ensure that the Query Store data collection process does not interfere with the normal processing of the Query Optimizer.

The one exception to this that I mentioned earlier is that the asynchronous process that captures queries and query plans also checks to see if there is plan forcing in place. We’ll cover that in a lot of detail in Chapter 7. Plan forcing is the one place where the Query Store does interfere with the normal optimization process. If plan forcing is in place for a query, then the plan being forced will be used instead of the plan generated by the Query Optimizer. That will also be the plan that gets written to the plan cache.

With that, the core processing of retrieving queries and query plans is complete. The information is captured by individual query, not by stored procedure or batch. If the query is part of an object, that object’s ID is captured along with the query information. We’ll cover the details of what exactly gets capture in the section titled “The Data Collected By Query Store” in this chapter.

Collecting Query Runtime Information and Wait Statistics

The collection of the runtime data is a little more straightforward than the query and query plan data collection. All the information collected about the runtime statistics and wait statistics occurs after the query and query plan are already captured. The query executes and then the Query Store process takes place as Figure 2-3 shows:

There is only one fundamental difference in the processing of this data collection and that of the query and query plan data. This is the timed asynchronous writes to disk. Instead of a process with minimal latency, the data is stored and aggregated in-memory for a period of time. The default is 15 minutes. You can control this and we’ll cover it in detail in Chapter 3.

The information , such as how long a query took to run and the waits experienced during the execution, is written to memory. That data is aggregated there in memory. When that information is written to disk, it gets reaggregated based on another setting, the collection interval. By default this is 60 minutes. What this aggregation gives you is the ability to compare two different time periods so that you can see if the performance of a query is changing over time. We’ll cover this in more detail in the “Data Collected” section.

General Notes on Data Collection

The Query Store works with all the other processes within SQL Server. So, for example, if a query is recompiling and it generates the same execution plan that is already in the Query Store, usage statistics are updated and nothing else happens. If, however, this recompile results in a new execution plan, then that plan will get captured to the Query Store just as the old plan did.

In the event of a cross-database query using three-part naming, the database from which the call is being made is where the query will be recorded, regardless of where the data being retrieved or modified lives. This is important so that you know where to look for Query Store information if more than one database is involved. You will not see the query in both databases.

Because all the data collection done in the Query Store gets written to disk asynchronously, it is possible to lose data because it has not yet been flushed to disk. There is a command to make this happen manually, sys.sp_query_store_flush_db. Executing that command will force anything currently in memory to be immediately written to disk. This ensures that before a controlled failover, shutdown, or any event that would result in the data being removed from memory, you can retain that data.

Query and Query Plan Information

As we’ve already stated previously, the foundation for the information gathered by Query Store is the query itself. You may pass a batch with five or ten statements, but each of those statements, assuming they’re all data manipulation statements, will be individual queries in the Query Store. The catalog views that expose the information available in Query Store are visible in Figure 2-4:

There are only four catalog views that expose the information stored on the query and the query plan. At the top and center, you can see sys.query_store_query. In the diagram only a few of the 30 columns are shown as examples of what information is available. Each query has attributes that may or may not be unique to the query. These attributes are the tables to the left and right of sys.query_store_query: sys.query_store_context_settings and sys._query_store_query_text. These attributes can be created once and then reused by multiple queries. This includes the query text. If other attributes change, but the query text remains the same, then more than one query in sys.query_store_query may have the same query_text_id value (query_id is the primary key). The query plans stored in sys.query_store_plan are tied directly to the query via a foreign key on query_id. Any given query may have multiple plans. Situations arise like parameter sniffing and others that result in a given query with more than one valid execution plan. The table shown in Figure 2-4 for sys.query_store_plan also only contains a sampling of the 23 columns available.

We’ll be covering querying these tables in detail in Chapter 5. However, it’s worth pointing out that some of the information may be a little misleading. If you look at both sys.query_store_query and sys.query_store_plan as shown in Figure 2-4, both have a column labeled count_compiles. While these values are labeled the same, they actually represent the difference between the results of a compile or recompile event for a query or for a plan. Every time a query gets compiled or recompiled, that value will be updated appropriately. However, that compile event may, or may not, result in the same plan. If a different plan gets compiled, then that plan gets its own distinct count_compiles value updated, not every plan associated with the query. Details like this we’ll be addressing throughout the book.

The rows of data stored in sys.query_store_plan actually act as the drivers for the runtime data collected. Let’s now take a look at that information.

Runtime Information

The runtime data actually gets aggregated based on two different values, the plan_id as mentioned in the previous section and the runtime interval that we talked about earlier (that has a default of 60 minutes). Figure 2-5 represents the information captured:

The model may look quite a bit more simplified, but it masks a few complexities that are worth noting. First up, the sys.query_store_runtime_stats catalog view shows only a small part of the 58 columns that make up the view. A lot of that data is laid out similar to what you see with the duration values: an average, a minimum, a maximum, and a standard deviation. That holds true for such interesting data as the CPU time, row count, memory, and more.

The next most important thing to keep in mind when dealing with the runtime data is that it is broken up into multiple aggregations as determined by the runtime interval. Since you’re unlikely to turn Query Store on at the top of the hour, the sys.query_store_runtime_stats_interval system view will have the start_time and end_time of each interval. While these will be the appropriate amount of time apart, 60 minutes by default, the start and stop times are not going to coincide with the clock.

Another thing to remember is that there are multiple time values to take into account with this information. In addition to the runtime interval, there is also the time to flush to disk, with a default of 15 minutes. That means that for any given plan_id, there may be data on disk across multiple time intervals as well as data in memory.

In the case of a query being aborted for any reason, the runtime data is still collected. However, it’s aggregated into a separate value for the given plan_id and for the given runtime_stats_interval_id. Because of this and the fact that there can be more than one row of runtime data between memory and disk, when you start writing your own queries against this data, you need to aggregate based on the plan_id, the execution_type, and the runtime_stats_interval_id.

Wait Statistics

We’ve separated the wait statistics from the runtime information only because some people using this book may be on SQL Server 2016. Figure 2-6 shows the layout of the information gathered for the wait statistics:

Just like with the runtime information, the wait statistics are aggregated by the runtime interval. Also, like the runtime information, wait statistics are dependent on the flush to disk interval, a default of 15 minutes, so will be aggregated in two locations. Again, like the runtime information, the execution_type also affects the aggregation of the wait statistics. Finally, the wait statistics add one more wrinkle. There is a wait_category that must be taken into account when aggregating the data across time intervals and execution_types. We’ll be covering all this in more detail in Chapter 5.

You can also see that there is a similar pattern to the data with a minimum, maximum, last, average, and standard deviation being stored. However, the wait time is the only interesting value. I left off a couple of descriptive columns in Figure 2-6 to save space. The rest of the information there is the important data for wait statistics.

Information About the Query Store

There is actually one final catalog view that we should mention, although the details of what it represents will be covered in Chapter 3. That view is sys.database_query_store_options. While it does represent some of the information gathered about the Query Store, it is not the focus of this chapter.