The Eye
UPDATE: After writing this and finding the results fishy, I reported the behavior described below in “Somewhat Surprising” and “Reciprocal?” and it was confirmed a defect in SQL Server 2019 CU8, though I haven’t tested earlier CUs to see how far back it goes. If you’re experiencing this behavior, you’ll have to disable UDF inlining in another way, until CU releases resume in the New Year.
With SQL Server 2019, UDF inlining promises to, as best it can, inline all those awful scalar UDFs that have been haunting your database for ages and making queries perform terribly.
But on top of the long list of restrictions, there are a number of other things that might inhibit it from kicking in.
For example, there’s a database scoped configuration:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON/OFF; --Toggle this
SELECT
dsc.*
FROM sys.database_scoped_configurations AS dsc
WHERE dsc.name = N'TSQL_SCALAR_UDF_INLINING';
There’s a function characteristic you can use to turn them off:
CREATE OR ALTER FUNCTION dbo.whatever() RETURNS something WITH INLINE = ON/OFF --Toggle this GO
And your function may or not even be eligible:
SELECT
OBJECT_NAME(sm.object_id) AS object_name,
sm.is_inlineable
FROM sys.sql_modules AS sm
JOIN sys.all_objects AS ao
ON sm.object_id = ao.object_id
WHERE ao.type = 'FN';
Somewhat Surprising
One thing that caught me off guard was that having the database in compatibility level 140, but running the query in compatibility level 150 also nixed the dickens out of it.
DBCC FREEPROCCACHE;
GO
ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 140;
GO
WITH Comments AS
(
SELECT
dbo.serializer(1) AS udf, --a function
ROW_NUMBER()
OVER(ORDER BY
c.CreationDate) AS n
FROM dbo.Comments AS c
)
SELECT
c.*
FROM Comments AS c
WHERE c.n BETWEEN 1 AND 100
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'), MAXDOP 8);
GO
Our query has all the hallmarks of one that has been inflicted with functions:
And if you’re on SQL Server 2016+, you can see that it executes once per row:
SELECT
OBJECT_NAME(defs.object_id) AS object_name,
defs.execution_count,
defs.total_worker_time,
defs.total_physical_reads,
defs.total_logical_writes,
defs.total_logical_reads,
defs.total_elapsed_time
FROM sys.dm_exec_function_stats AS defs;
Reciprocal?
There’s an odd contradiction here, though. If we repeat the experiment setting the database compatibility level to 150, but running the query in compatibility level 140, the function is inlined.
DBCC FREEPROCCACHE;
GO
ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 150;
GO
WITH Comments AS
(
SELECT
dbo.serializer(c.Id) AS udf,
ROW_NUMBER()
OVER(ORDER BY
c.CreationDate) AS n
FROM dbo.Comments AS c
)
SELECT
c.*
FROM Comments AS c
WHERE c.n BETWEEN 1 AND 100
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'), MAXDOP 8);
GO
Rather than seeing a non-parallel plan, and non-parallel plan reason, we see a parallel plan, and an attribute telling us that a UDF has been inlined.
And if we re-check the dm_exec_function_stats DMV, it will have no entries. That seems more than a little bit weird to me, but hey.
I’m just a lowly consultant on SSMS 18.6
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.
