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';
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;
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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.