Scalar UDF Inlining

Overview

Scalar UDF inlining is the largest free performance win in SQL Server 2019, and it is almost entirely invisible. A function either qualifies or it does not. The engine will not tell you which unless asked, will not tell you why not at all, and will not tell you what the refusal is costing. sys.sql_modules.is_inlineable is one bit in a catalog view nobody reads.

This report answers three questions, in this order:

1. Is inlining even switched on here. Compatibility level 150 or higher, and the database scoped configuration TSQL_SCALAR_UDF_INLINING set to ON. If either is off then nothing else on the page matters yet, and the headline says so before anything else does.
2. Which functions are already inlining, and which are one setting away from it.
3. For the ones that are blocked, what is blocking them, and which single edit buys the most. That last question is what the chart is for.

Two things about the numbers are worth stating plainly rather than leaving to be discovered.

sys.dm_exec_function_stats stops reporting a function once it is being inlined, because there is no longer a function call to count. Every cost figure on this page is therefore cost still being paid on un-inlined calls. A function that is already inlining correctly has none, which is the point rather than a gap in the data.

is_inlineable is the engine’s own answer and the report treats it as final. What the engine does not expose is the reason, so the reason is worked out here: part from the catalog views, the rest by scanning the module text. Where the scan comes up empty the function gets a Reason not detected row rather than a guess, because a report that attributes a blocker to the wrong cause sends somebody off to rewrite a function that will still not inline afterwards.

---

Where to find it

Route	How
Server tree	Expand a database → Real Time → Scalar UDF Inlining
Report arrows	Previous is Plan Cache, next is SPs by Logical Writes

The page title reads Scalar UDF Inlining for <database name>.

The tree node only appears on SQL Server 2019 and newer. On anything older it is not drawn at all, which is also what keeps the page out of the crash sweep that walks the same tree.

---

Requirements

• SQL Server 2019 or newer. sys.sql_modules.is_inlineable is the only place the engine says whether a function qualifies, and it does not exist before 2019. The report checks for the column itself on entry rather than trusting a cached version number.
• VIEW DEFINITION on the functions, for the module text. Without it the definition comes back empty and blocked functions fall through to Reason not detected.
• VIEW SERVER STATE on the instance, for sys.dm_exec_function_stats. Without it every cost column is empty and the chart falls back to counting functions, which it will say in the subtitle.

Nothing is installed on the monitored instance and nothing on this page runs anything against the database. Raising a compatibility level re-plans every query in the database, and rewriting a function is a code review. Both are somebody’s change window, not a button on a report. The enable script is offered as text to take away and read.

---

Reading the matrix

Every other database-level chart in this product ranks objects: worst table first, worst index first. That is the right shape when the finding is which object. Here it is not. Inlining is decided per function but fixed per edit, and one edit fixes a set. Strip GETDATE() out of five functions and five functions start inlining. A ranked bar chart of eighteen functions cannot say that, and it cannot say the opposite either: that the function at the bottom of the list carries four different blockers and is a rewrite rather than a fix.

So the mark is a set intersection matrix.

The rows

One per reason a function can fail to inline, ordered by how many blocked functions carry it. The bar at the right of each row is that count, under the heading FUNCTIONS CARRYING IT.

Only reasons that actually occur are drawn. A database with nothing recursive in it does not get an empty Recursive row.

The columns

One per distinct combination of reasons that occurs in this database. Not one per reason, and not one per function. The bar above a column is what those functions are still costing, and the x5 under it is how many of them there are.

The leftmost column is pinned and green. It is not a combination of blockers, it is the absence of one: functions that are inlineable and are not being inlined, because the database is below compatibility level 150, because the scoped configuration is off, or because somebody wrote WITH INLINE = OFF on the function itself. It is the only column on the page whose fix is a setting rather than a rewrite, so it does not compete with the rest for position.

Everything after it is sorted by weight, biggest first, so the leftmost red column is always the edit worth making first.

The two orderings disagreeing is itself informative. A blocker sitting on nine functions whose column is only the third bar means those nine functions are barely called.

What the bars measure

Whatever the toolbar says: CPU, calls, or a straight count of functions. The heading over the bars changes with it, so a bar height is never unlabelled:

Toolbar	Heading over the bars
CPU	CPU STILL SPENT ON FUNCTION CALLS
Calls	CALLS STILL BEING MADE
Count	FUNCTIONS IN THIS COMBINATION

If the plan cache holds nothing for any of these functions, CPU and Calls would draw a row of bars all the same height because they are all zero. The chart falls back to counting functions and the subtitle says so:

no function statistics are cached, so the bars count functions

That is the difference between a fallback and a wrong chart.

The zone strip

Above the matrix, one full-width strip split by verdict, with the counts beside it, each with its own colour swatch so colour never carries the meaning alone:

inlining now · one setting away · blocked · no usage found

The strip covers every scalar function in the database. The matrix below covers only the blocked and one-setting-away ones, so the page never loses track of how many functions there are even though two bands are deliberately kept out of the chart.

The column limit

The matrix draws at most 18 columns, the pinned one included. Past that the columns are narrower than a dot and the connectors stop being followable. The tail is folded into one grey column and the footer says how much was folded:

The grey column folds 6 further combinations covering 11 functions – click it to see them in the grid.

Never a silent truncation. Click the grey column to see everything in it in the grid.

If there is nothing to draw, the chart says so:

Every scalar function in this database is inlineable.

---

The blockers

Eleven reasons, each with what it would have to become. That second line is the one the rewrite checklist is built from, and it is the second line of the row tooltip, because a row label on its own says what is wrong and says nothing at all about what to do next.

Blocker	What it would have to become
Time-dependent intrinsic	Pass the time in as a parameter and let the caller decide it once.
Side-effecting intrinsic	Compute the value at the call site; a function that cannot be replayed cannot be inlined.
Unsupported statement	Only DECLARE, SET, SELECT, IF..ELSE and RETURN are allowed. A loop usually wants to become set-based, or an inline table-valued function called with CROSS APPLY.
Table variable or TVP	The table variable usually wants to become a join or an EXISTS.
Variable accumulation	Assigning a variable from itself across rows has no defined order. STRING_AGG or a windowed aggregate at the call site does the same job.
XML method	Shred the XML at the call site rather than inside the function.
EXECUTE AS not CALLER	EXECUTE AS CALLER is the only setting that inlines. Anything else needs the permission handled another way.
User-defined type	A parameter or the return value is a user-defined type; the base type inlines.
Natively compiled	Natively compiled functions are already compiled; inlining does not apply to them.
Recursive	The function calls itself. There is nothing finite to inline.
Reason not detected	The engine refused and the definition scan could not say which rule it breaks. Open the definition and compare it against the inlineable scalar UDF requirements by hand.

Time-dependent intrinsic is the commonest by a long way. GETDATE() inside a scalar function is close to a default habit, and it is usually the cheapest thing on the page to fix.

How the reasons are worked out

Four of them are catalog fact and are not subject to the text scan finding or missing anything: EXECUTE AS, natively compiled, recursive, and user-defined type. A table-valued parameter is also catalog fact and stands whether or not there was any module text to read.

The rest come from scanning the module text, and the scan is careful in three ways worth knowing about:

• Only the body is scanned, everything from the first BEGIN after RETURNS. The header carries WITH EXECUTE AS OWNER and WITH INLINE = OFF, and looking for the word EXECUTE across the whole definition would hang an Unsupported statement on every function that has an EXECUTE AS clause.
• Comments, string literals and bracketed identifiers are blanked out first, so a function carrying -- replaced GETDATE() with a parameter in its header does not collect a blocker for the comment, and a column called [GETDATE] is not a hit. Block comments nest in T-SQL, so the depth is counted rather than stopping at the first close.
• Matching is on word boundaries throughout. MyGetDateHelper is not a hit for GETDATE, and TRY_CAST and TRY_CONVERT are not hits for TRY, because the underscore counts as part of the word.

An intrinsic also has to be followed by an opening bracket to count. GETDATE without one is somebody’s variable or column name.

---

The four verdicts

Verdict	Colour	What it means
Inlining	Green	Inlineable, and actually being inlined. Nothing to do.
One setting away	Amber	Inlineable and not being inlined. A setting, not a rewrite.
Blocked	Red	Not inlineable. Needs a code change.
No usage found	Grey	Nothing in the plan cache has called it and no module in the database references it.

No usage found is grey rather than green, so dead code never reads as a pass mark. It is deliberately not called unused: the plan cache is not a record of everything that has ever run, so the honest claim is that a search found no usage, not that there is none. Both tests have to fail before a function lands here, because an empty plan cache alone is not enough to write a function off.

The inlining test is applied before the dead-code test, on purpose. A function that is being inlined has no entry in sys.dm_exec_function_stats by definition, so testing for dead code first would relabel every working function as never called.

---

The toolbar

Button	What it does
All	Every function. The default.
Blocked	Only the ones that need a code change.
One setting away	Only the ones a setting would fix.
Inlining	Only the ones already working.
No usage found	Only the ones nothing appears to call.
CPU / Calls / Count	What the matrix bars measure.
Refresh	Re-read now.

A band button for a band with nothing in it is disabled. All stays pressable whatever happens, because it is where a filter set from the chart goes home to.

Filtering is a different view of rows already fetched, so every button on this toolbar except Refresh redraws without going back to the server.

---

Reading the grid

Column	What it is
Schema	The schema the function is in.
Function	The function name.
Status	The verdict, with its colour swatch beside the word.
Blockers	Every blocker this function carries, joined with dashes. Empty when it is inlineable.
Executions	Calls recorded in the plan cache. Empty rather than zero when there are none.
CPU	Total worker time, drawn with a share gauge against the busiest function on the page.
CPU / call	Worker time divided by the call count.
Reads / call	Logical reads divided by the call count.
Called by	How many modules in this database reference it.
Returns	The return data type.
Last run	Last execution recorded in the plan cache.
Modified	When the function was last altered.

The list is sorted blocked first, because it is the only band that needs a decision, then by what each function is costing, then by how many things call it. A function with no cache entry but forty callers outranks one nothing has ever touched. Dead code sorts last.

Called by counts modules in this database only. A recursive function referencing itself is taken back out of the count, because a function calling itself is not somebody calling it, and counting it would stop the function ever being recognised as dead code. Application code that calls a function directly never appears: nothing in SQL Server records that.

Dates are shown as yyyy-MM-dd HH:mm rather than in the machine’s locale, because the grid sorts these columns as text and a locale that puts the day first sorts every January together.

---

Chart and grid interactions

Gesture	Result
Hover a column	How many functions, which blockers with their hints, what they are still costing, and the largest one named
Hover a row	The blocker, what it would have to become, how many carry it, and how many carry nothing else
Click a column	Filters the grid to that combination; click again to clear
Click a row	Filters the grid to every function carrying that blocker; click again to clear
Double-click a column	Opens the definition of the first function in it
Double-click a row	Opens the definition of the first blocked function carrying it
Right-click anywhere on the chart	Copy Chart to Clipboard
Select a grid row	Highlights the matrix column that function landed in
Double-click a grid row	Opens that function’s definition

The row tooltip carries the number that decides whether a blocker is worth attacking:

9 blocked functions carry it. 4 of them have nothing else wrong with them.

Or, when it is not worth attacking on its own:

Every one of them carries at least one other blocker as well, so fixing this alone would not make any of them inline.

---

Right-click actions in the grid

Item	What it gives you
Copy function definition	The module text, with the verdict and every blocker written across the top as comments.
Copy inlining status script	What the engine itself says about this function, and the two database settings that decide whether being inlineable turns into being inlined.
Copy “who calls this” script	Everything in the database that references it, through sys.dm_sql_referencing_entities. The blast radius of a rewrite.
Copy enable-inlining script	Both database-wide switches, commented out. Only offered when one of them is actually off.
Copy rewrite checklist	What was found on this function and what each one would have to become, numbered. Only offered when the function has blockers.
Copy function name	Just the bracketed name.

Every script is investigation only and every statement that changes anything is commented out. The enable script names the compatibility level change as the larger of the two by a long way:

WARNING: raising the compatibility level changes far more than scalar UDF inlining. It changes the cardinality estimator and every query optimizer behaviour gated behind the level, so it re-plans the whole database. Capture a Query Store baseline first, and expect to spend time on the regressions.

It also names the escape hatch, which is worth knowing before turning inlining on database-wide: inlining can be turned off one function at a time with WITH INLINE = OFF, so a single function that regresses does not have to cost you the setting.

---

The definition window

Double-clicking anything opens the module text with what the report found written across the top of it. A definition on its own is what SSMS already gives; the header is what this page adds.

-- [dbo].[FormatCustomerName]
-- Blocked
--
-- Time-dependent intrinsic: Pass the time in as a parameter and let the caller decide it once.
-- Unsupported statement: Only DECLARE, SET, SELECT, IF..ELSE and RETURN are allowed. ...
--
-- 4.2M calls, 3h 35m of CPU, 3.1 ms per call.

For a function created WITH ENCRYPTION the module text is not readable, and the window says so rather than showing an empty box:

— The module text is not readable. This function was created WITH ENCRYPTION.

An encrypted function that will not inline gets the Reason not detected row, which is the truth.

---

How to read the report

1. Read the headline first. If it says nothing in this database is being inlined, the whole blocker discussion below it is premature. Fix the switch, then come back.
2. Look at the zone strip. How much of this database is already fine, and how much is not.
3. If the green column is there, start with it. Those functions need no code change at all.
4. Otherwise take the leftmost red column. It is the heaviest, which makes it the edit worth making first.
5. Hover the row that spans the most columns. If a good number of its functions carry nothing else, that one blocker is the cheapest win on the page.
6. Click into the column to see the functions in the grid, and double-click the biggest to read what it actually does.
7. Copy the rewrite checklist for the function you are going to change. It lists what was found on that specific function rather than the general rules.
8. Check is_inlineable afterwards. It is the only thing that settles whether the rewrite worked. Check it rather than assuming.

Common patterns

A tall green column and nothing else. Every function in the database qualifies and none of them are inlining. This is a setting, not a project. Read the enable script and plan the regression testing.

One wide row across most of the columns. A single habit spread through the codebase, usually GETDATE(). The functions carrying only that blocker are a batch of small identical edits.

A short column with an enormous bar. One or two functions carrying an unusual combination, called constantly. Worth a rewrite on its own merits regardless of how few functions it covers.

A tall column with a tiny bar. Many functions, barely called. Real work, little payoff. Come back to it after the heavy columns.

Mostly grey in the zone strip. A lot of scalar functions nothing appears to call. Before rewriting any of them, find out whether they are called from application code, which nothing in SQL Server records.

A large Reason not detected row. The engine is refusing and the scan cannot say why. Open the definitions and compare them against the inlineable scalar UDF requirements by hand. Encrypted modules land here by definition.

---

Where the data comes from

One query per load, against the catalog views and the plan cache:

• sys.objects and sys.schemas, filtered to type = 'FN' and is_ms_shipped = 0
• sys.sql_modules for is_inlineable, inline_type, native compilation, the EXECUTE AS principal, and the module text
• sys.parameters and sys.types for table-valued and user-defined parameters, and the return type
• sys.sql_expression_dependencies for the caller count and for recursion
• sys.dm_exec_function_stats for the costs, pinned to this database
• sys.database_scoped_configurations and DATABASEPROPERTYEX for the two switches

Only T-SQL scalar functions are shown. CLR scalar functions cannot be inlined under any circumstances, and inline table-valued functions are a different feature with a confusingly similar name. Either would put rows on the page whose verdict could never change.

Every cost join is a left join. A function with nothing in the plan cache belongs on this report; losing it would hide exactly the blocked-but-uncached case.

Nothing is stored. The costs come from the plan cache, which is emptied by a restart, by memory pressure, and by anyone running DBCC FREEPROCCACHE. There is no history behind this page.

---

Settings

Setting	Default	Values
ScalarInliningWeight	0	0 = CPU, 1 = Calls, 2 = Count

Per-user rather than per-database, so switching databases keeps the same weight. The band filter, the chart filters and the grid sort are not remembered.

Dark mode and colour-blind mode change every colour but nothing else. The colour-blind palette is the same Tol set the sibling reports use.

---

Messages you may see

On an instance older than SQL Server 2019, reached by a route that does not go through the tree:

The Scalar UDF Inlining report requires SQL Server 2019 or newer. This instance is running 14.0.3456.2. Scalar UDF inlining was introduced in SQL Server 2019, and sys.sql_modules.is_inlineable does not exist on older versions.

When the database has no scalar functions:

There are no T-SQL scalar functions in this database. CLR scalar functions and table-valued functions are not shown – neither can be inlined.

When a refresh empties whatever the filter was sitting on:

No scalar functions match the current filter. The All button has the rest.

---

Related reports

Report	Why you would go there
CPU by Query	Where the CPU a blocked function is burning actually shows up.
Plan Cache	What else is in the cache these function statistics come from.
Stored Procs by Logical Writes	The procedures most likely to be calling these functions.
Table Use	What the functions are reading from.

---

Frequently asked questions

Why does a function that is inlining show no CPU? Because sys.dm_exec_function_stats stops reporting a function once it is being inlined. There is no longer a function call to count. That is the feature working, not missing data.

Why is a function marked Blocked when I cannot see anything wrong with it? is_inlineable is the engine’s answer and this report does not argue with it. If the reason column says Reason not detected, the definition scan could not identify which rule it breaks; compare the function against the inlineable scalar UDF requirements by hand.

Can I turn inlining on from this report? No. The enable script is offered as text with every statement commented out. Raising a compatibility level re-plans every query in the database, and that is a change window with a regression test plan behind it, not a button.

Do I have to raise the compatibility level to get inlining? Yes, to 150 or higher, and it is by far the bigger of the two changes. The scoped configuration is the small one and it is reversible in a single statement.

What if one function regresses after inlining? Turn it off for that function alone with WITH INLINE = OFF rather than turning the database setting back off. The enable script names this.

Why are only some combinations shown? The matrix draws at most 18 columns. Anything past that is folded into the grey column, and the footer says how many combinations and functions it covers. Click it to see them all in the grid.

Why is a blocker row’s bar longer than the dots in its row account for? The row bar counts every blocked function carrying that blocker, including the ones in folded combinations. It is telling the truth about the tail.

Does No usage found mean the function is safe to drop? No. It means nothing in the plan cache has called it and nothing in this database references it. Application code calling a function directly is not recorded anywhere in SQL Server.

Why are the bars counting functions when I picked CPU? Because nothing in the plan cache has called any of the functions in the chart, so CPU would draw every bar at zero. The subtitle says so when it happens.

---