I may have occasionally talked about the importance of Query Store, but today I want to emphasize just how much Microsoft is weaponizing query store.
Of course, I don’t mean they’re creating the Death Star or something, I simply mean they’re taking the information that Query Store gathers and using that to enable a number of new performance enhancements within SQL Server.
Intelligent Query Processing
Starting back in SQL Server 2017, Microsoft started making improvements to how queries get processed. The early stuff was mostly about interleaved execution, basically allowing for the ability to adjust an execution plan on the fly. This was expanded quite a bit in 2019. Follow the link for a great graphic showing how this stuff has progressed.
SQL Server 2022, releasing soon I’m sure (no, no inside knowledge on this at all, but, we’re near the end of the year, and it has been named, and there is a large-ish data focused event coming up in a couple of weeks, adding two to probably two, I’m getting probably real soon), expanded the whole Intelligent Query Processing a ton.
The beauty and intelligence behind all this is very clear and easy to understand. The fact is, building good data structures is either hard, or too late. Writing well functioning T-SQL is either hard, or, again, too late because the code is out there. So very many people build poor structures and write bad code. Once it’s released, there’s no going back. But, everyone desperately wants their bad stuff to run faster, now. Not after they learn the right way to build a structure and then migrate all their data into this new, faster database. Now!
OK, Microsoft has said. What if we make the optimizer and the query engine take into account all this bad code? Or even, what if we acknowledge that some aspects of our tool, parameter sniffing as a screaming example, can actually cause real pain? What if we can mitigate both these issues? And what if we used Query Store to do it?
Weaponizing Query Store
If we look at the cool stuff added to SQL Server 2022 for Intelligent Query Processing we see:
- Parameter Sensitive Plan Optimization (aka Fixing Parameter Sniffing)
- Optimized Plan Forcing
- Percentile Grant Feedback
- Persistance Mode Feedback
- Degree of Parallelism Feedback
What do all these have in common? They all require Query Store be enabled on the database in order for you to see the benefits they offer. In short, Microsoft has put Query Store to work.
Now, you’ll see the query engine noticing that a plan change resulted in serious performance degradation, because they can use Query Store to measure before and after that plan change. They can see if the cardinality estimations are good, or not, and adjust them through plan forcing. The query engine will be able to adjust the Degree of Parallelism, on the fly, helping cope with what has always been an issue, regardless of how good your code is.
In short, Microsoft is weaponizing Query Store.
Conclusion
So what’s your takeaway here? Two things. First, if you have zero knowledge of Query Store, right now is a very good time to get going on that. Second, if you are not using Query Store in your systems now, I would suggest you start testing it. I’m not saying that Intelligent Query Processing solves all your problems, so you must upgrade to 2022. I am saying it may alleviate some serious pain points for many people, so getting a full understanding of how it’s base, Query Store, functions, and specifically functions in your environment, is important.
The good news is, there are tons of resources to learn Query Store. I helped on a book, mostly written by Tracy Baggiano, just on Query Store. My new book on query tuning has a ton of Query Store in it, as well as explanations of the details of all this Intelligent Query Processing stuff. Heck, I’m doing a session at the PASS Data Community Summit in two weeks on, you guessed it, Query Store. If you do a search, there are ton of other resources online. I would especially point out Erin Stellato as someone you should be paying attention to.
Query Store isn’t flawless. It can cause problems. There are knobs & tweaks you can use to adjust its behavior. Oh, and did I mention, it’s going to be on by default in SQL Server 2022? While it’s not perfect, it is pretty darned great. I do recommend you use Query Store. Time to get to learning.