With each recent version of SQL Server, Microsoft has added features that can be used to improve query performance with much less effort than traditional index and query tuning require. First was Query Store, introduced with 2016. When enabled, this feature allows you to easily find regression in query performance due to changing execution plans. You then can force a better performing plan for a particular query. In 2017, you can enable this feature to automatically use a better performing plan when one exists.

Along with forcing execution plans, another 2017 feature is called Adaptive Query Processing.  Adaptive Query Processing changes how queries are optimized in certain situations. (I was lucky enough to watch Bob Ward demonstrate this feature at SQLBits a few months ago and would really love for one of my customers to upgrade so I can see this feature in action on a production workload.)

One of the new optimizations involves multi-statement table valued functions. When I’m query tuning, the use of multi-statement table valued functions is a red flag for me. These are almost always a bad idea, especially when used in a join or cross apply, but they are often used by developers because of reusability. The biggest problem is that, before 2017, the optimizer always estimates the results of one of these functions to be exactly 100 rows. This affects the plan downstream of the function, including memory grants and join types (nested loop vs. hash mash, for example). Adaptive Query Processing solves this problem by using what Microsoft calls an ‘interleaved execution’ approach. Instead of just using the 100-row estimate, the function will be executed during optimization and then that information will be used to come up with the rest of the plan. Now, with a better row estimate, the plan will be more appropriate for the query.

Instead of multi-statement table valued functions always being quite the disaster in a query, they might perform better using this feature. I’ve seen so many situations where the function contains a while loop and conditional logic, and the optimization will not help this situation, but it is still quite an improvement over the 100 rows and done approach. As someone who has done quite a bit of query tuning over the years, I want to say to developers “Just don’t use them. No, keep using them – more query tuning work for me!”

Interleaved execution is just one of three optimizations available with Adaptive Query Processing in SQL Server 2017. The two others are ‘batch mode adaptive joins’ and ‘batch mode memory grant feedback.’ To learn more about these, take a look at this video from Joe Sack.

Adaptive Query Processing really adds a new level of intelligence to the optimizer. I can’t wait to see what else Microsoft has in store!