Query Store 

If you are working with SQL Server 2016 or higher, even Express, there is a new native tool that you should know about: Query Store. Query Store is a feature that can be enabled on any SQL Server 2016 instance or higher, even when compatibility mode is lower. In this blog post, we will explore the features of Query Store and how to configure it.

Query Store Features:

Query Store is a native tool that captures historical query information. Unlike previous native tools, data captured by Query Store will outlive performance changes, failovers, reboots, and even SQL upgrades because data is stored in tables inside each database.

There is minimal I/O impact since the data collected is stored in memory then asynchronously persisted to disk. Query Store gathers two important parts of historical query information. First, we have plan details with stats detailing if a seek or scan was performed and the number of plans our queries have in cache. Then we have runtime stats like start time, CPU, duration, IO, compile counts.

Query Store is not enabled by default but can be used by anybody with view database state permission. Once inside, we can review our data and identify queries with multiple plans that have regressed over time or are parameter sensitive. We can override the query optimizer’s preferred query plan by forcing query execution plans for specific queries. If needed, we can even compare activity over different time frames and search based on a sproc that ran a query.

SQL Server 2019 Query Store can include wait stats too. Once we have pinpointed the queries negatively impacting performance, we can easily take a full backup of any Query Store enabled database then restore it to a lower level environment for tuning. Now we have the history, along with the plans and statistics from our queries available for testing and troubleshooting.

Query Store Configurations:

There are several configurations for Query Store. Trace flag 7745 by default will flush data to disk every 15 minutes and TF7752 to improve Query Store load times when SQL Server is rebooted. The default cleanup mode is “Auto,” which will prevent Query Store from running out of storage space.

If we exceed Query Store's max size, this will automatically switch from the default setting of read/write mode to read-only, preventing new query data collections. Capture mode is another configuration with a default setting of “All,” which will capture all queries.

Conclusion:

Query Store is a powerful tool that can help you to optimize your SQL Server database. By capturing historical query information, you can identify queries with multiple plans that have regressed over time or are parameter sensitive. You can also override the query optimizer’s preferred query plan by forcing query execution plans for specific queries. With Query Store, you have the history, plans, and statistics from your queries available for testing and troubleshooting. So, if you are working with SQL Server 2016 or higher, even Express, enable Query Store and start optimizing your database.