The Paradox of Abstraction in SQL Server

For SQL Server, however, the source text is only the description of a result. How the result is obtained is decided by the optimizer, and it sees your encapsulation differently than you do. This creates a paradox:

• Too little visible: A function can hide its logic from the optimizer. It can neither estimate nor rearrange the work inside, calls it once per row in the worst case, and gives up parallelism.
• Too much visible: A view is expanded before optimization. Three lines of SQL on a deeply nested view become a query with dozens or hundreds of table references. In the worst case, the search for a plan alone takes longer than its execution.

Both have the same cause: the structure of your code and the structure of the execution plan are two different things. This page shows how to recognize both sides of the paradox and leads to the deep dives with measurements on SQL Server 2017 through 2025. You do not need any PSG tool for this. One layer up, between application and database, the same question arises with Entity Framework; that is covered by the topic page Entity Framework & SQL Server.

Three questions instead of one

“Is the query fast?” does not tell you where the problem lies. Three questions help more:

A query can be cheap to execute and still hard to optimize. Conversely, the optimizer sometimes finds a plan quickly that is bad because information is missing. The deep dives show both cases in the execution plan.

The deep dives

Which observation matches yours?

Inventory: Where does your database hide or multiply work?

The query lists three things in the current database: scalar functions that were actually executed row by row, all T-SQL functions with their inlining eligibility, and the views with their nesting depth. It is a starting point for the search, not a verdict: a deep view or a scalar function is only a problem once the execution plan shows it.

Query: Inventory of functions and views

/* PSG · The Paradox of Abstraction · Query 1   Inventory: Which functions and views hide or multiply work?    - Read-only. Changes nothing.   - Run it in the user database you want to examine (not in master).   - Returns three results:       1 Scalar functions that were actually executed row by row (since the last restart or plan cache entry)       2 All T-SQL functions with type and, from SQL Server 2019, inlining eligibility       3 Views with nesting depth and number of table references after expansion   - Permissions: VIEW DEFINITION on the objects; for result 1 additionally VIEW SERVER STATE     (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough).   - From SQL Server 2017. Tested in the PSG lab on 2017, 2019, 2022, and 2025.*/ /* 1 · Scalar functions with row-by-row execution   sys.dm_exec_function_stats only shows scalar functions that ran as a separate call.   Inlined scalar functions and table-valued functions do not appear here:   If a function is missing, that does not mean it causes no work. */SELECT TOP (25)    OBJECT_SCHEMA_NAME(fs.object_id, fs.database_id) AS schema_name,    OBJECT_NAME(fs.object_id, fs.database_id)        AS function_name,    fs.execution_count                               AS calls,    fs.total_worker_time / 1000                      AS cpu_ms_total,    fs.total_elapsed_time / 1000                     AS elapsed_ms_total,    fs.total_logical_reads                           AS logical_reads,    CAST(fs.total_worker_time / 1000.0 / NULLIF(fs.execution_count, 0) AS decimal(12,3)) AS cpu_ms_per_call,    fs.cached_time                                   AS cached_sinceFROM sys.dm_exec_function_stats AS fsWHERE fs.database_id = DB_ID()ORDER BY fs.total_worker_time DESCOPTION (MAXDOP 1); /* 2 · T-SQL functions with type and inlining eligibility   FN = scalar function, IF = inline table-valued function, TF = multi-statement table-valued function.   is_inlineable only applies to FN and only from SQL Server 2019 (otherwise NULL).   Eligibility does not mean that a specific call is inlined: only the actual plan shows that. */IF COL_LENGTH('sys.sql_modules', 'is_inlineable') IS NOT NULL    EXEC (N'    SELECT  SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS function_name, o.type AS type, o.type_desc,            CASE WHEN o.type = ''FN'' THEN m.is_inlineable END AS is_inlineable,            (SELECT CAST(c.value AS int) FROM sys.database_scoped_configurations AS c              WHERE c.name = ''TSQL_SCALAR_UDF_INLINING'') AS inlining_enabled_in_db    FROM sys.objects AS o JOIN sys.sql_modules AS m ON m.object_id = o.object_id    WHERE o.type IN (''FN'', ''IF'', ''TF'')    ORDER BY o.type, schema_name, function_name    OPTION (MAXDOP 1);');ELSE    SELECT  SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS function_name, o.type AS type, o.type_desc,            CAST(NULL AS bit) AS is_inlineable    FROM sys.objects AS o    WHERE o.type IN ('FN', 'IF', 'TF')    ORDER BY o.type, schema_name, function_name    OPTION (MAXDOP 1); /* 3 · Views: nesting depth and what is behind them   depth                 = longest chain view -> view -> ... down to the table   views_below           = how many distinct views come along when expanding   distinct_tables       = how many different tables are behind it   function_refs         = distinct functions in the chain   Limit: The metadata records each reference only once. If one view uses another twice   (e.g. through a self-join), you only see that in the execution plan.   The recursion ends after 32 levels; functions are counted as an endpoint, not expanded. */WITH edges AS (    SELECT DISTINCT d.referencing_id, d.referenced_id, o.type AS target_type    FROM sys.sql_expression_dependencies AS d    JOIN sys.views   AS v ON v.object_id = d.referencing_id    JOIN sys.objects AS o ON o.object_id = d.referenced_id    WHERE d.referenced_id IS NOT NULL      AND o.type IN ('U', 'V', 'IF', 'TF', 'FN')), expanded AS (    SELECT k.referencing_id AS view_id, k.referenced_id AS target_id, k.target_type, 1 AS level    FROM edges AS k    UNION ALL    SELECT a.view_id, k.referenced_id, k.target_type, a.level + 1    FROM expanded AS a    JOIN edges AS k ON k.referencing_id = a.target_id    WHERE a.target_type = 'V' AND a.level < 32)SELECT TOP (50)    OBJECT_SCHEMA_NAME(a.view_id)                                  AS schema_name,    OBJECT_NAME(a.view_id)                                         AS view_name,    MAX(a.level)                                                   AS depth,    COUNT(DISTINCT CASE WHEN a.target_type = 'V' THEN a.target_id END)  AS views_below,    COUNT(DISTINCT CASE WHEN a.target_type = 'U' THEN a.target_id END)  AS distinct_tables,    COUNT(DISTINCT CASE WHEN a.target_type IN ('IF', 'TF', 'FN') THEN a.target_id END) AS function_refsFROM expanded AS aGROUP BY a.view_idORDER BY depth DESC, distinct_tables DESCOPTION (MAXDOP 1, MAXRECURSION 100);

How to read the result:

• Result 1 names the scalar functions that ran as a separate call, with call count and CPU time since their plan has been in the cache. Many calls with little CPU per call are the typical picture of a function that runs per row of a large query. The call count is not always the row count: in the lab, the same function appeared twice in the plan, and for 1,000 orders the query counted 2,000 calls. Inlined functions are missing here, and so are table-valued functions. After a restart, or when the plan falls out of the cache, the list is empty.
• Result 2 lists all T-SQL functions. is_inlineable = 1 only means that SQL Server can embed the function. Whether a specific call does so is shown by the actual plan. inlining_enabled_in_db shows whether inlining is enabled in this database. On SQL Server 2017, is_inlineable stays empty because there is no inlining there.
• Result 3 shows the 50 most deeply nested views with the tables behind them. A view with depth 6 and 20 tables is a candidate for looking at its queries in the plan. The metadata does not reveal whether a view uses another one several times; only the plan shows that.

An excerpt of result 3 from the lab database of the deep dive Nested views:

vw_L16 and vw_S16 are chains of 16 views over 17 and 18 tables; the inventory correctly shows them as deep and wide. vw_R9 looks harmless with two tables. In fact, each of its nine levels uses the level below twice, and the plan contains the orders table 256 times. A narrow query on it compiled for more than three minutes in the lab. The inventory cannot see exactly this case.

Four assumptions that do not hold

“A function is executed once.” A scalar function without inlining runs once for each row it has to compute. In a query over 100,000 orders, that is up to 100,000 calls, each with its own overhead.

“A view is precomputed.” An ordinary view stores only its definition. With every query, it is expanded and optimized together with all the views below it. Only an indexed view stores results.

“Inlinable means inlined.” From SQL Server 2019, the optimizer can embed many scalar functions into the calling query. Whether that happens depends on the function, on the call, on the compatibility level, and on the configuration.

“Short SQL text, simple plan.” The length of the query says nothing about the size of the task the optimizer has to solve after expanding all views.

What keeps encapsulated code fast

• Keep the abstraction, but choose the right one. An inline table-valued function encapsulates logic without hiding it: the optimizer treats it like a view with parameters. When it is the better choice is shown in T-SQL functions.
• Check the plan, not the source text. Whether a function was embedded, whether a view lost joins, whether the optimizer gave up early: all of this is in the actual execution plan.
• Keep views flat. A reporting view built on three others is quickly written and expensive for a long time. How foreign keys help the optimizer remove unnecessary joins again is shown in Nested views.
• Recheck with every upgrade. New versions change what the optimizer does with functions. That often helps, but not always. Measure on your own code.
• Look at the bigger picture. If the server as a whole is slow, the SQL Server Performance Guide leads through the appropriate diagnostic paths.

Sources

• Microsoft: Scalar UDF Inlining
• Microsoft: Interleaved execution for multi-statement table-valued functions
• Microsoft: sys.dm_exec_function_stats
• Microsoft: Troubleshoot optimizer timeouts
• Microsoft: Views