SQL Server 2017: Interleaved Execution for MSTVFs

What I don’t want you to take away from this

Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them.

• Backed by table variables
• Lots of hidden I/O cost
• Number of executions may surprise you

One important current limitation (May-ish of 2017) is that Interleaved Execution doesn’t happen when you use MSTVFs with Cross Apply. It’s only for direct Joins to MSTVFs as far as I can see.

But does it improve anything?

Well, kinda.

Let’s get physical

I’ve got this stinker over here. It stinks. But it gets me where I’m going.

I wanted to run different kinds of queries to test things, because, well Adaptive Joins require Batch mode processing which right now is limited to ColumnStore indexes.

“Good” news: Interleaved Execution doesn’t require a ColumnStore index.

Confusing news: They still get called Adaptive Joins.

Yeah. Words. More pictures.

Old plans

I lied. More words first.

Dropping compatibility levels down to 130 (I know, down to 130, what a savage) pulls Interleaved Execution out of the mix.

Running against both a ColumnStore index version of Users and a Row Store version…

Here’s the stats time and IO results.


Table 'Users_cx'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 626, lob physical reads 0, lob read-ahead reads 0.
Table 'Users_cx'. Segment reads 1, segment skipped 0.
Table '#AC8B865E'. Scan count 1, logical reads 754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1250 ms, elapsed time = 1996 ms.



Table 'Users'. Scan count 0, logical reads 888887, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#AC8B865E'. Scan count 1, logical reads 754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2344 ms, elapsed time = 2742 ms.


Alright, pictures.

Isn’t it weird and confusing when you get these missing index requests on queries that use ColumnStore indexes? I can’t figure out who’s wrong.

Are my queries that bad? Does the optimizer not have a rule about this? Did it break the rule because my query is so bad?

Why… why do you wanna have a different index, here, SQL? What’s on your mind?

What if I add the index?

EVERYTHING GETS WORSE

The plan looks just like the Row Store plan from before, and has the same stats time and IO pattern.

Lesson learned: Stop listening to missing index requests when you’re using ColumnStore.

New Plans

Calgon, take me away.


Table 'Users_cx'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 626, lob physical reads 0, lob read-ahead reads 0.
Table 'Users_cx'. Segment reads 1, segment skipped 0.
Table '#B1503B7B'. Scan count 1, logical reads 754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1488 ms, elapsed time = 1633 ms.



Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B1503B7B'. Scan count 1, logical reads 754, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 145, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1530 ms, elapsed time = 1592 ms.


Exciting already! The ColumnStore index is right about in the same place, but the RowStore index is much faster.

But why?

This part is pretty cool!

In the first plan, the optimizer chooses the ColumnStore index over the nonclustered index that it chose in compat level 130.

This plan is back to where it was before, and I’m totally cool with that. Avoiding bad choices is just as good as making good choices.

I think. I never took an ethics class, so whatever.

In the second plan, there’s yet another new index choice, and the cpu and IO profile is down to being competitive with the ColumnStore index query.

The optimizer realized that with more than 100 rows coming out of the MSTVF, it might be a good idea to use a more efficient index than the PK/CX. Another good move. Way to go, optimizer. Exactly how many rows did it estimate?

The Operators

An Interleaved Execution plan doesn’t have any special operators, but it does have special operator properties.

Hovering over the topmost Table Valued Function operator, that’s where some of the new properties live.

Even though this is all in Row mode, the Join type is Adaptive. I’m guessing that Adaptive Join is going to be an umbrella term for new reactive optimizations.

Maybe. I’m guessing.

One thing you want to pay extra attention to here is the estimated number of rows.

It’s not 100 anymore.

It’s the actual number of rows that end up in the table variable.

Ain’t that somethin?

The bottom Table Valued Function operator doesn’t have the Interleaved property, but it does show that it’s an Adaptive Join, and we have the correct estimate again.

Not bad

If you have a lot of MSTVFs in legacy code that you don’t have time to untangle, SQL Server 2017 could save you a ton of time.

This is a huge improvement over what you used to get out of MSTVFs — I wonder if something similar might be coming to regular old Table Variables in the near future?

UPDATE: Them’s bugs! Calling interleaved execution joins Adaptive Joins is an error, and will be fixed in a future version of SSMS.

Thanks for reading!