Mar 08 2024 02:21 AM - edited Mar 11 2024 01:05 AM
I have been trying to improve the performance of a slow view that suffers from massive use of one particular scalar UDF (600 uses in the view).
- DB compatibility is at 160
- hosted on Azure SQL (i.e. 2022 engine)
- Server configuration allows inlining (TSQL_SCALAR_UDF_INLINING = ON)
- No disabling HINT is being used
- sql_modules shows is_inlineable = 1
And yet the execution plan for the view query shows NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable" and is riddled with <UserDefinedFunction> entries so inlining is clearly not happening. What possible reason can the engine have not not inlining the udf?
So obviously the next thing I did was run a very basic query using the udf and hey presto, it is inlined.
After some experimentation I came to the conclusion that there is a magic number, and that number is 99. If a query contains 99 or fewer calls to a mixture of *any combination* of udfs, then inlining is possible. 100 or more in the query and both inlining and parallelism seem to be switched off.
I have searched for documentation or an explanation of this apparent limit in the hope of finding a workaround, config option or hint, without success.
Can anyone shed any light on this?
Mar 10 2024 11:13 PM
Mar 11 2024 01:03 AM
@olafhelper The content of the scalar udfs isn't relevant to the question. I've experimented with a mix of udfs including some string manipulation, maths, value lookups and other common patterns including those in Microsoft samples. All show as 'inlineable' and will properly inline in smaller queries. It doesn't matter in what combination they are used in a query, the magic limit of 99 calls is applied by the engine and beyond this inlining and parallelism doesn't happen.
I'm hoping someone recognises this behaviour and can point me at official documentation for this limit so I can understand it and potentially explore workarounds. So far I have been unable to find anything on it.