Introduction
Query Store is a new feature in SQL Server 2016, which once enabled, automatically captures and retains a history of queries, execution plans, and runtime statistics, for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after a server restart or upgrade.
In the last article of the series, I discussed the advantages of this new feature, and explained some of the scenarios where you can use it. Finally, I talked about data capture processing, including what’s captured, and how it is retrieved when you enable the Query Store feature. In this article I am going to explain query execution flow when using Query Store and how it differs from regular query execution flow, its architecture and how to get started with it.
Query Store – Query Execution Flow
On the left side of the figure shown below, you can see how query execution works in earlier versions of SQL Server or even in SQL Server 2016 when Query Store is not enabled. As this flow is quite straightforward and you have been seeing it for ages, I will jump into explaining how it differs when you enable the Query Store feature for a database.
As you can see on the right side execution flow in the figure below, during compilation, when a query is compiled for the first time, the query text along with all relevant properties and its initial plan are stored into the Query Store internal tables (database-scoped persistent store). During query execution, runtime statistics are captured and stored into the Query Store internal tables after query execution. Query Store keeps aggregated statistics accurate for every plan that was executed within the currently active time interval (according to the time granularity you have defined).
Figure 1 – Query Execution Flow
When a query gets recompiled and if a new plan is created, Query Store adds the new plan entry for the query in its internal tables (database-scoped persistent store), keeping the previous ones along with their runtime execution statistics. During the compile and check for recompile phases, SQL Server determines if there is a plan available in the Query Store that should be applied for the currently running query and if yes, then it makes use of it.
If there is a forced plan and the plan in the procedure cache is different than the forced plan, the query gets recompiled, effectively the same way as if PLAN HINT was applied to that query with Plan Guide. This process happens transparently to the user application.
Query Store – Performance Troubleshooting Workflow
When you want to start utilizing the Query Store feature for troubleshooting performance problems in the database, you need to first enable it at the database level (you can do it either with T-SQL or in SSMS as discussed next in this article) and then let the regular workloads get executed – which you want to troubleshoot. Once the Query Store feature is enabled, it starts collecting query information and its execution statistics. You can choose to either use newly introduced catalog views or you can use inbuilt, out-of-box dashboards (discussed in detail in the next article of this series) for analyzing the collected data. If needed you can force one specific plan to be used for a query in subsequent executions by SQL Server Query Processor, if you think or conclude that a specific plan is the most optimum for that query.
Figure 2 – Query Store – Performance Troubleshooting Workflow
Query Store – Let’s Get Started
There are multiple ways to enable the Query Store feature for a database. For example, you can use SQL Server Management Studio (SSMS) to enable it using graphical user interface or you can write T-SQL scripts to do it manually or in an automated way.
In the Object Explorer of SSMS, right click on the database (for which you want to enable Query Store) and then click on the Properties menu. On the Database Properties page, click on the Query Store tab as shown in the below figure. By default, this feature is disabled as you can notice. Query Store feature can be enabled in either of these two operation modes:
- Read-Write – In read-write mode, the Query Store collects and persists the information (as we discussed earlier).
- Ready-only – In read-only mode, the persisted information can be read from the query store for analysis, but no new information is captured or added. Query Store gets switched to read-only mode if the maximum allocated space of the Query Store has been exhausted.
You can also see some other useful information like storage space occupied by the database and by Query Store internal tables. You can click on Purge Query Data to empty, or purge, all the data inside the Query Store.
Figure 3 – Database Properties: Query Store
You can also write a T-SQL script if you prefer scripting or want to automate the process, as shown below. You can replace the AdventureWorks2016 database name with the name of your database for which you want to enable Query Store feature. There are a few other important properties to be specified when making use of Query Store, which I would like to highlight here. For a complete list of these properties you can refer to this official documentation.
- OPERATION_MODE – It is the same as discussed above, which could be either OFF, READ_ONLY, and READ_WRITE.
- CLEANUP_POLICY – It allows you to configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the Query Store.
- MAX_STORAGE_SIZE_MB – It allows you to configure the maximum size of the data stored in the Query Store. If the data in the Query Store hits the MAX_STORAGE_SIZE_MB limit, the Query Store stops collecting new data as it switches operation mode from read-write to read-only automatically.
- DATA_FLUSH_INTERVAL_SECONDS – It allows you to specify the frequency at which the captured data, written to in-memory portion of the Query Store is persisted or transferred to disk asynchronously (it is done better performance).
- INTERVAL_LENGTH_MINUTES – It allows you to specify the granularity at which runtime execution statistics are aggregated and stored into the Query Store. To optimize for space usage, the runtime execution statistics are aggregated over a fixed time window or time interval.
ALTER DATABASE [AdventureWorks2016] SET QUERY_STORE = ON GO ALTER DATABASE [AdventureWorks2016] SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 31), MAX_STORAGE_SIZE_MB = 1024, DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = AUTO, INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO ) GO
Once enabled, you can see the status, properties and storage size occupied on the Database Properties page as you can see in the figure below. You can click on the Purge Query Data button or execute ALTER DATABASE [AdventureWorks2016] SET QUERY_STORE CLEAR command to remove or purge the content of Query Store. You can also use EXEC sys.sp_query_store_flush_db command explicitly to flush the content of the memory portion of the Query Store data to disk to persist it.
Figure 4 – Database Properties
Note: The feature mentioned and demonstrated in this article is based on SQL Server 2016 Release Candidate 0 and might change when RTM will be available or in future releases.
Conclusion
Query Store is a new feature in SQL Server 2016 which, once enabled, automatically captures a history of queries, execution plans, and runtime statistics, retaining them for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after server restart or upgrade.
In this article of the series, I explained query execution flow when using Query Store and how it differs from regular query execution flow, I talked about typical performance troubleshooting workflow with Query Store and how to get started with it. In the next article of the series, I am going to demonstrate how you can analyze the collected data by Query Store either with T-SQL scripting and with graphical user interface in SSMS.
