According to Microsoft, “The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server.”
The issue is: With SQL Server 2016 through 2019, it only does this automatically if you turn it on. Meaning: it’s set to off by default.
Don’t leave it off.
This is a powerful performance resolution tool every DBA should be familiar with and using. Sometimes it’s left off just because people don’t know about it. Sometimes it’s left off for fear of adding overhead that will slow down the system. That simply isn’t the case. And even if there is some overhead, the advantages of using Query Store far outweigh that concern.
Think of Query Store as a database black box. It captures data to help you analyze problems as soon as they occur. You can in essence “go back in time” to review possible issues. It’s available with on-premises SQL Server and is also a key tool to use when troubleshooting performance issues in Azure SQL Database!
In short, it’s one of the most useful performance tools a DBA could use. Query Store contains two distinct stores: a plan store for persisting the execution plan information, and a runtime stats store for persisting the execution statistics information.
If that’s all Greek to you, we invite you to watch a Query Store-focused webinar hosted earlier this year by Senior Consultant Dan Maenle.
Turn it on and get all the benefits it provides!
This Post Has 2 Comments
Pingback: Using Accelerated Database Recovery | SQL Solutions Group
Pingback: Following a Structure When Performance Tuning, Part 1 | SQL Solutions Group