Every environment is configured differently; however, we have all experienced alerts being triggered due to unfavorable performance. Oftentimes, before our root cause analysis begins, the issue has resolved itself and/or someone may have rebooted. Although current performance is ideal, this or another issue may surface in the future. But how can we gather information and determine the root cause after the fact or a reboot flushed everything from the cache? Well, SQL Server 2016 has provided us with a great solution!
Welcome to Query Store, a new native tool available with all 2016 SQL Server versions, yes even Express! This feature is enabled in SSMS at the database level giving granular control on which databases we collect data from. Query Store can be enabled on any SQL 2016 instance or higher, even when compatibility mode is lower, which is great for migration prep.
Query Store Features
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 basically gathers two important parts of historical query information, great for proactive and reactive performance tuning. 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, and much more. We can use t-sql or the GUI to filter and analyze our data based on the initial complaint(s) of degraded performance or proactive performance tuning.
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.
Important Query Store Configurations
As with most tools, we can modify the configurations to suit your workload better, so here are a few configs to keep in mind. 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 Stores 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. However, for your environment using “Auto,” which will capture infrequent queries with insignificant compiles, or creating a custom data collection policy may be best.
Performance tuning is a process of elimination that often leads back to our queries. Query Store will make this process much more efficient with the historical data it captures. If you’re on SQL 2016, enable Query Store, and if you’re not, what are you waiting for?