Brent Ozar Unlimited - Today's posts from Brent Ozar Unlimited for 05/16/2017

Today's posts from Brent Ozar Unlimited for 05/16/2017
Here's our latest blog post.
Is this email not displaying correctly?
View it in your browser.
Erik Darling says:

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

But is it faster?

Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions.

A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.

Before you huff and puff, I’ve seen cases where a scalar valued function was faster than either other kind of function.

Of course, Jeff Moden was involved, so there is clearly black magic at play here. Or maybe just some more beer popsicles.

Unfortunately… Or, I don’t know, maybe fortunately, Microsoft doesn’t seem to have been putting much development time into improving the performance characteristics of scalar valued functions. Just little bits.

Us vs Them

Let’s get back to the point at hand, though. MSTVFs have been improved in certain circumstances. Inline table valued functions are the reigning champions.

How do they stack up?

I’m going to take my best-timed MSTVF with Interleaved Execution, and put it up against an inline table valued function.

Here are my functions.

CREATE FUNCTION [dbo].[MultiStatementTVF_Join] (@h BIGINT)
RETURNS @Out TABLE (UserId INT, BadgeCount BIGINT )
AS
    BEGIN
        INSERT  INTO @Out
                (UserId, BadgeCount)
        SELECT  UserId, COUNT_BIG(*) AS BadgeCount
        FROM    dbo.Badges AS b
        GROUP BY b.UserId
        HAVING COUNT_BIG(*) > @h
        RETURN;
    END;

CREATE FUNCTION [dbo].[ITVF_Join] (@h BIGINT)
RETURNS TABLE
AS
    RETURN
        SELECT  UserId, COUNT_BIG(*) AS BadgeCount
        FROM    dbo.Badges AS b
        GROUP BY b.UserId
        HAVING COUNT_BIG(*) > @h;

Here are my queries.

SELECT u.Id, mj.*
  FROM dbo.Users_cx AS u
  JOIN dbo.MultiStatementTVF_Join(0) mj
  ON  mj.UserId = u.Id
WHERE u.LastAccessDate >= '2016-12-01'

SELECT u.Id, mj.*
  FROM dbo.Users_cx AS u
  JOIN dbo.ITVF_Join(0) mj
  ON  mj.UserId = u.Id
WHERE u.LastAccessDate >= '2016-12-01'

Start your (relational) engines

First, here’s are the stats on my MSTVF


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 '#B42CA826'. 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 = 1117 ms, elapsed time = 1122 ms.

Here’s the execution plan (I collected this separately from getting the CPU timing).

No funny business

Second, here are the stats on my inline table valued function.


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 'Badges'. Scan count 1, logical reads 1759, 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 = 250 ms, elapsed time = 271 ms.

Here’s the execution plan (again, collected this separately from getting the CPU timing).

Double filtered for your pleasure

Well then

In this case, the inline table valued function wiped the floor with the MSTVF, even with Interleaved Execution.

Obviously there’s overhead dumping that many rows into a table variable prior to performing the join, but hey, if you’re dumping enough rows in a MSTVF to care about enhanced cardinality estimation…

Picture my eyebrows.

Picture them raising.

Thanks for reading!

Register for GroupBy June to see sessions on JSON, containers, columnstore, CI, and more.

Read the comments.

Erik Darling says:

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.

CREATE FUNCTION [dbo].[MultiStatementTVF_Join] (@h BIGINT)
RETURNS @Out TABLE (UserId INT, BadgeCount BIGINT )
AS
    BEGIN
        INSERT  INTO @Out
                (UserId, BadgeCount)
        SELECT  UserId, COUNT_BIG(*) AS BadgeCount
        FROM    dbo.Badges AS b
        GROUP BY b.UserId
        HAVING COUNT_BIG(*) > @h;
        RETURN;
    END;

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…

ALTER DATABASE SUPERUSER SET COMPATIBILITY_LEVEL = 130
GO

  SELECT u.Id, mj.*
    FROM dbo.Users_cx AS u --ColumnStore!
    JOIN dbo.MultiStatementTVF_Join(0) mj
    ON  mj.UserId = u.Id
  WHERE u.LastAccessDate >= '2016-12-01'

  SELECT u.Id, mj.*
    FROM dbo.Users AS u --RowStore!
    JOIN dbo.MultiStatementTVF_Join(0) mj
    ON  mj.UserId = u.Id
  WHERE u.LastAccessDate >= '2016-12-01'
GO

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.

But you say he’s just a friend

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.

ALTER DATABASE SUPERUSER SET COMPATIBILITY_LEVEL = 140
GO

  SELECT u.Id, mj.*
    FROM dbo.Users_cx AS u --UltraVox!
    JOIN dbo.MultiStatementTVF_Join(0) mj
    ON  mj.UserId = u.Id
  WHERE u.LastAccessDate >= '2016-12-01'

  SELECT u.Id, mj.*
    FROM dbo.Users AS u --RegularVox!
    JOIN dbo.MultiStatementTVF_Join(0) mj
    ON  mj.UserId = u.Id
  WHERE u.LastAccessDate >= '2016-12-01'

GO


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?

Choices, choices

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.

Ain’t nothin special

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

I promise this will be in sp_BlitzCache

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.

ooh barracuda

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.

Twitter is for work.

Thanks for reading!

Register for GroupBy June to see sessions on JSON, containers, columnstore, CI, and more.

Read the comments.

Copyright © 2017 Brent Ozar Unlimited®, All rights reserved.
We're sending you this because you signed up for the BrentOzar.com Community. If you no longer want these community recaps, please unsubscribe. We're all about staying at Inbox Zero too.
Our mailing address is:
Brent Ozar Unlimited®
9450 SW Gemini Dr
ECM #45779
Beaverton, OR 97008

Add us to your address book

About Brent Ozar Unlimited

Brent Ozar Unlimited's highly specialized experts focus on your goals, diagnose your toughest IT pains, and prescribe remedies.