Legal Notice
I’ve trademarked: Froidrage, Froidulent, and Froidpocalypse.
If you want to use them, you have to pay me $10,000.
Alright, I’m being told by my lawyer that writing them on cocktail napkins and showing them to confused bartenders doesn’t actually register a trademark.
Nevermind.
Here’s What’s Gonna Happen
And it’s not a problem that you need SQL Server 2019 to see. All you have to do is try to rewrite a function.
Here’s our Villain, a scalar UDF.
CREATE FUNCTION dbo.Villain (@UserId INT) RETURNS INT WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN DECLARE @Score INT SELECT TOP (1) @Score = p.Score FROM dbo.Posts AS p WHERE p.OwnerUserId = @UserId AND p.PostTypeId = 1 ORDER BY p.Score DESC; RETURN @Score; END GO
Here’s the query that’s gonna call it:
SELECT TOP ( 10 ) u.DisplayName, u.Reputation, dbo.Villain(u.Id) FROM dbo.Users AS u ORDER BY u.Reputation DESC; GO
I’m Going To Show You Two Things
The estimated plan, and the actual plan.
I need to show you the estimated plan so you can see what the function does, because that’s not included in the actual plan.
Yes, the estimated plan is more accurate than the actual plan.
Marinate on that.
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2019/06/2019-06-15_11-01-05.png)
The important thing is the second plan, which is the function’s execution plan. Notice that it generated a missing index request, and doesn’t spool anything at all.
It handles the query logic with a Top N Sort.
Here’s the actual plan:
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2019/06/2019-06-15_11-02-26.png)
Let’s talk about a couple things:
- A nonclustered index scan that costs 100% and runs for 0.000s
- A compute scalar that costs 0% and runs for ~3s
The compute scalar thing is well documented by… Well, not by official Microsoft documentation.
But they’ve been blogged about by Cookies Cunningham, and Paul White.
Thanks, you two.
Any Reasonable Person
Would say “I can rewrite that function and make things better”.
Because of course an inline function is always better than a scalar function.
Enter our Hero.
CREATE FUNCTION dbo.Hero (@UserId INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT TOP (1) p.Score FROM dbo.Posts AS p WHERE p.OwnerUserId = @UserId AND p.PostTypeId = 1 ORDER BY p.Score DESC; GO
Here’s the query that’s gonna call it:
SELECT TOP ( 10 ) u.DisplayName, u.Reputation, h.* FROM dbo.Users AS u CROSS APPLY dbo.Hero(u.Id) AS h ORDER BY u.Reputation DESC;
I Only Need To Show You One Thing
Since the function is an inline type, the query processor is honest with us about the full query plan.
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2019/06/2019-06-15_11-14-50.png)
Two things happened here:
- The “function body” no longer goes parallel
- The TOP (1) is run against an eager index spool rather than the clustered index
What’s The Point?
This is what FROID does for you without a rewrite. It’ll inline the scalar UDF.
The plan may be better, or it may be worse.
The scalar UDF plan ran for 3 seconds, and the inline version ran for almost 13 seconds.
Stay tuned for tomorrow’s post. I have a couple suggestions for how The SQL Server team can help end users stay on top of these problems in SQL Server 2019.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.