on April 4, 2018
It’s tough to keep track of which features work in each version of SQL Server, and which Editions support them.
My memory told me that the new Adaptive Joins feature in SQL Server 2017 was Enterprise Edition only… and that’s correct, but I didn’t realize that the fancy new feature to make Multi-Statement TVFs smarter has much wider licensing.
Interleaved Execution for Multi-Statement Table Valued Functions works in Enterprise, Standard, Web, and Express Editions
I discovered this by accident when I was doing some testing on my Standard Edition instance. I was looking at an execution plan, and I checked the properties of a TVF and …
First, I double-checked and made sure I really was connected to my Standard Edition instance. YEP.
Then I checked the SQL Server 2017 Editions and Components chart to see if this was an accident or not
Sure enough, ‘Batch Mode Adaptive Joins’ and ‘Batch Mode Memory Grant Feedback’ are Enterprise Edition features.
‘Interleaved Execution for Multi-Statement Table Valued Functions’ is available in all the editions.
What does ‘Interleaved Execution for Multi-Statement TVFs’ do for you?
Multi-statement table valued functions are difficult to optimize as part of a query: they run multiple statements, so how the heck do you estimate how many rows are going to come out of that?
This new feature lets the optimizer stop and take a peek!
When the optimizer finds a multi-statement TVF that qualifies*, it pauses the optimization process (badass!), and then goes briefly into the execution phase.
(This is the interleaving part.) It can run through that little part of the plan to get a good estimate, instead of making a blind guess.
Good estimates means a much better chance that you’ll get the right joins, memory grant, and qualify for parallelism when needed.
That usually adds up to faster query execution.
Which Multi-Statement TVFs qualify?
Not all multi-statement TVFs are created equal. If your TVF is part of a data modification, or if your TVF is inside a CROSS APPLY, it doesn’t qualify for this magic – at least not in SQL Server 2017. That may change in the future, this is just the V1.
How do I turn this on?
If you’re on SQL Server 2017, your database compatibility level controls whether or not this feature is available.
If your compat mode is 140, your multi-statement table valued functions are very likely to speed up.
I say ‘very likely’ because there can be some edge cases where better estimates lead to worse performance.
Want to learn more?
- Read Joe Sack’s detailed post on Interleaved Execution for Multi-Statement TVFs
- Check out Joe’s Adaptive Query Processing demos on GitHub, if you want to get your hands dirty in a test environment