It’s December. The last month of the year, and hence also the last T-SQL Tuesday of the year. Edition 181 is hosted by Kevin Chant. His chosen topic for this episode is to have us talk about which SQL Server announcement excited us the most.
This posed a bit of a problem for me. The only truthful answer here is that I have never been as excited about an announced new feature as when the Query Store was announced. But I also realized that I don’t have much to write about the Query Store, except how awesome it is. Okay, I could go on a full explanation, but many others do that much better, and have already done so. So that doesn’t leave much for me.
And that’s why I’ll also include my second favorite announcement.
Query Store
The Query Store was announced in, I think, 2015, when SQL Server 2016 was announced. It was often described as the black box for SQL Server. Not a term I really liked. A black box is used to investigate root causes after a plane crashes. I prefer when SQL Server does not crash, and I typically use the Query Store to investigate bad performance, or even just to monitor performance (if a client has no better monitoring tools available). I would not want to compare bad performance to a crash. And I think that the Query Store would actually be of limited use as an investigation tool if SQL Server does indeed crash.
I could now go on a tutorial on how to set up and use the Query Store. But I won’t. As I already said, that information is already out there, and I don’t see the benefit of adding yet another copy to the internet. So let me instead give you just a few examples of how I have used the Query Store to help my clients.
A customer asked me to look at a process that had taken too long. They had sent me the six queries that executed during that process, and the approximate time frame (“yesterday between two and six”). They had forgotten to send me information about the order of those six queries. Luckily, the query text of each of those queries contained fragments that were unique enough that I could find them in sys.query_store_query_text, and then join that to the to find the other information I needed to find the duration and the execution time (which I always have to remind myself is the end time, not the start time!). Compute the start time from those, and I had all the information I needed to make an overview of the order in which the queries executed, which ones ran at the same time, which ones had to wait for another, and so on.
At another time, I was working for a customer, but they had not yet collected all the information to set me on a targeted assignment. So, while waiting for that information, I just started to check the Query Store for queries with high resource usage. Their focus was always on processes that took long to complete, so they never would have identified the two queries that I found that way. Queries that did a table scan on an admittedly rather small table (very volatile, but typically between 100 and 200 rows). The scan was fast enough, just a few milliseconds. But it executed literally thousands of times per second! The query looked for a single row, and by creating an index to support those two queries, the total resource usage on that server went down by somewhere in the order of magnitude of 10%. Without the information in the Query Store, that query would probably never have been on anyone’s radar!
The last example is more an “if only” scenario. A nightly load process had its normal duration, but every once in a while (typically once or twice per week), one or more steps would suddenly take one, two, or even three hours longer than normal, even though the data volumes were the same. I would have loved, at that time, to dig into the Query Store for the queries in that process and compare the wait statistics between regular executions and slow executions. Unfortunately, that customer was still on SQL Server 2016, and tracking of wait statistics was only added in SQL Server 2017.
That said, in the end the Query Store did still provide some clues that helped me solve the issue. I once more looked at duration and end time, to compute the start time. And those times did not match with the log kept by the scheduling tool that kicked off the queries. On slow days, there sometimes were gaps of over an hour between the time the scheduler kicked off a stored procedure, and the start time of the first query in that stored procedure as recorded in Query Store. That mismatch had me banging my head against the wall for a long time – until I finally figured out that the Query Store records the time the query starts executing, which is after the execution plan has been compiled – which in turn is after any necessary statistics updates have been done. I wrote about that here. Again, I don’t know if I would have figured this out without the data in the Query Store.
New execution plan features
As promised, here is my other favorite announcement. Except that this is not actually a single announcement, it is more a moving target, or perhaps a class.
You might know by now that I love execution plans. Part of that love is, of course, because they are a great way to dig into a bad performing query and figure out the root cause of the bad performance, so I can make it go faster. That is my job after all, and I enjoy being good and effective at it. But there is also a much geekier part of that love. And that is simply because I really enjoy digging into the details and internals of execution plans, and figuring out how everything works.
That’s why, whenever I look at, read, listen to, or otherwise consume what Microsoft announces for a new version, or as a new Azure SQL Database feature, I instantly focus on if and how that feature affects or is exposed in execution plans “does this affect execution plans, and if so, then how?”
Microsoft has treated me well in that respect. In SQL Server 2012, there were many new features that affect execution plans. Columnstore indexes of course, along with batch mode execution and even a new operator (that has since been removed): Batch Hash Table Build. But SQL Server 2012 also introduced windowing extensions to the OVER clause, that required yet another new operator: Window Spool.
In SQL Server 2014, there were no huge visible changes. But for someone who looks below the surface, at the properties, there still was a lot of news. Such as extending batch mode to several new operators, allowing the Hash Match operator to build its own hash table (which was the death warrant for the Batch Hash Table Build operator), and adding support for memory-optimized tables and indexes to all scan and seek operators. I also liked the addition of the Actual I/O Statistics property to each operator that (potentially) does I/O.
SQL Server 2016 then saw the introduction of the Query Store discussed above, which also affects the execution plan (albeit in a minor way). I was also quite happy with the new QueryTimeStats and WaitStats plan properties, as well as the EstimateRowsWithoutRowGoal and Memory Usage operator properties.
The big new thing in SQL Server 2017, from an execution plan perspective, was the set of features collectively know as Adaptive Query Processing. Another new operator was introduced: Adaptive Join. And the other new features, Memory Grant Feedback, and interleaved execution for multi-statement table-valued functions, all resulted in more new properties.
The next version extended Adaptive Query Processing to now be Intelligent Query Processing. Several new features were added, such as Table Variable Deferred Compilation, Batch Mode for Row Store, Approximate Count Distinct, and (the most interesting one from my perspective, despite its initial flaws and its many limitations) Scalar Function Inlining. Some of these had visible effects in the execution plan, others had less effect in the plan itself but had amazing impact on the behavior. Either way, all were very interesting to investigate.
The last new version so far is SQL Server 2022. The biggest new thing here was Parameter Sensitive Plan Optimization, but let’s also not forget the approximate percentile functions, and Query Processing Feedback.
And I am sure I have forgotten many things in this overview. I am just typing down what pops into my head. There has simply been too much to remember it all!
And now SQL Server 2025 has been announced. I can’t wait to get my hands on a build, so I can start experimenting with new features, and see if and how they impact execution plans. Features such as Optional Parameter Plan Optimization, native JSON support, regular expressions, and vector datatypes are high on my list to look at. And I will of course keep my eyes and ears open for any other announcements in the near or far future!
But first … fingers crossed that I get accepted in the private preview program, so I don’t have to wait until the public CTEs.
Conclusion
Not surprising, my favorite announcements all are related to execution plans. The Query Store really tops the list. But I have such a geeky interest in execution plans that basically every new announcement has me itching my fingers to start playing with the new feature and searching for traces of it in the execution plan!
Thanks for selecting this topic, Kevin. And let me also (a bit early, I know) wish every reader of this blog a very happy (and pager-free!) festive season!
