Nested Views: Three Lines of SQL, One Huge Plan

SELECT *FROM dbo.vw_SalesReportingWHERE CustomerID = 42;

What the optimizer receives is something else. Before it looks for a plan, SQL Server replaces every view with its definition, level by level. Three lines become one query with all the joins, subqueries, and expressions of all the layers. This deep dive shows what happens, when it goes well, and how you can tell that the optimizer is overwhelmed.

A view is not a precomputed module

An ordinary view stores only its definition, no data and no plan. It is expanded on every query, and the optimizer sees only the tables behind it. That is often a good thing: it can push filters down, reorder joins, and leave out parts the query does not need. It also means that every layer brings its work into every query built on top of it.

Only an indexed view stores results. It is a tool of its own, with its own rules and write costs, and not the topic of this page.

When the optimizer removes joins again

If the query asks for only a few columns, the optimizer can leave out joins to tables from which nothing is needed. This is called join elimination, and it requires the optimizer to know for certain that the join does not change the row count:

• For an inner join, it needs a trusted foreign key (created or checked WITH CHECK) on columns that are NOT NULL.
• For an outer join (LEFT JOIN), a unique key on the right side is enough.

In the lab, there was a chain of up to 16 views. Each level builds on the previous one, adds a dimension table through an inner join, and outputs its name. The query needs only the order number and the name from the first dimension:

-- Level 1: orders and the first dimensionCREATE VIEW dbo.vw_L1 ASSELECT o.OrderId, o.D1Id, /* … */ o.D16Id, o.Amount,       d1.Name AS D1NameFROM dbo.Orders AS oJOIN dbo.D1 AS d1 ON o.D1Id = d1.Id;GO -- Level 2: the level below plus the second dimensionCREATE VIEW dbo.vw_L2 ASSELECT v.OrderId, v.D1Id, /* … */ v.Amount, v.D1Name,       d2.Name AS D2NameFROM dbo.vw_L1 AS v   -- builds on the view belowJOIN dbo.D2 AS d2 ON v.D2Id = d2.Id;GO -- … and so on up to level 16CREATE VIEW dbo.vw_L16 ASSELECT v.OrderId, /* … */ v.D15Name,       d16.Name AS D16NameFROM dbo.vw_L15 AS vJOIN dbo.D16 AS d16 ON v.D16Id = d16.Id;

-- Trusted: created WITH CHECK, column D1Id is NOT NULLALTER TABLE dbo.Orders WITH CHECK    ADD CONSTRAINT FK_Orders_D1    FOREIGN KEY (D1Id) REFERENCES dbo.D1 (Id);   -- exactly one D1 per order-- … likewise for D2 to D16 -- Check: is_not_trusted must be 0SELECT name, is_not_trusted, is_disabledFROM sys.foreign_keysWHERE parent_object_id = OBJECT_ID('dbo.Orders');

SELECT OrderId, D1Name   -- two columns out of 16 levelsFROM dbo.vw_L16WHERE OrderId BETWEEN 1000 AND 1100;

SELECT o.OrderId, d1.Name AS D1NameFROM dbo.Orders AS oJOIN dbo.D1 AS d1 ON o.D1Id = d1.IdWHERE o.OrderId BETWEEN 1000 AND 1100;

SELECT *   -- now every dimension is needed, nothing drops outFROM dbo.vw_L16WHERE OrderId BETWEEN 1000 AND 1100;

The same chain existed twice: once with trusted foreign keys from the orders to all dimensions, once without. At 16 levels:

“Flat” is the same query directly on the two tables it needs.

With foreign keys, none of the 16 joins remained. The plan read only the orders and the first dimension, 9 pages, like the flat query. Without foreign keys, it read all 17 tables and 3,237 pages. With 101 rows, that is only milliseconds. The difference becomes clearer when the query groups over all orders: with foreign keys, it ran as fast as the flat query, 25 to 64 ms. Without foreign keys, it took 107 to 155 ms on SQL Server 2019 to 2025, and 918 ms on 2017.

The compile time stands out. Even though all the needless joins are removed in the end, the view query compiled 20 to 50 times as long as the flat one. SQL Server first expands all levels and removes what is not needed afterward. The elimination saves work at execution, not at compilation. If the query needs all columns, there is nothing to remove: then the view ran just as fast as the flat query, but compiled about twice as long.

In production, compilation happens only when no suitable plan is in the cache. For a query that runs a thousand times with the same plan, 50 ms is a one-time cost. It becomes noticeable for queries that are recompiled often, for example with changing literals in the SQL text or with OPTION (RECOMPILE).

In the lab, SQL Server 2025 compiled most queries for longer than 2022, depending on the query by 1.1 to 1.6 times. Only the deepest diamonds without foreign keys (see below) compiled faster on 2025. We did not investigate the reason.

When the search gets too big

The optimizer does not try out all possible plans. It works in stages and has a budget for each query that depends on its size. When the budget is used up, it takes the best plan found so far. The actual plan then shows:

• StatementOptmLevel = FULL and StatementOptmEarlyAbortReason = TimeOut, in the graphical plan “Reason For Early Termination: Time Out”.

This is not an error and not an abort of the query. The plan found can be good. It only means that the optimizer did not check all possibilities, and that small changes to data or statistics can lead to a completely different plan.

In the lab, TimeOut came early and independently of views. Even the query written flat on 9 tables ended with it on all four versions. In the view chain without foreign keys, it occurred from 8 levels on, with foreign keys never, because only two tables remained after the elimination. What matters, then, is how many tables the optimizer has to arrange in the end, not how the query is written. A TimeOut alone is therefore not a finding but a hint to look more closely.

Microsoft describes real errors for very large expanded queries:

• 8623: The optimizer finds no plan at all (“ran out of internal resources”).
• 8632: The expanded expression exceeds an internal limit.

In the lab, neither occurred, not even for queries that contained more than 500 table references after expansion and compiled for over three minutes. The limit was time, not an error. Anyone waiting for an error message to recognize a problem with nested views may therefore wait in vain.

Views used more than once multiply the work

It becomes especially expensive when a view uses another view more than once, for example for a comparison with the previous year. Each use is expanded individually. If another view builds on it and does the same, the table references double with every level. You cannot see this in the source text; the inventory on the overview page does not show it either. Only the execution plan shows how often the same table is read.

In the lab, each level of this diamond joined the view below it to itself on the order number. The result stays the same, one row per order:

CREATE VIEW dbo.vw_R1 ASSELECT o.OrderId, d1.Name AS D1Name, o.AmountFROM dbo.Orders AS oJOIN dbo.D1 AS d1 ON o.D1Id = d1.Id;

CREATE VIEW dbo.vw_R2 ASSELECT a.OrderId, a.D1Name, a.Amount + b.Amount AS AmountFROM dbo.vw_R1 AS aJOIN dbo.vw_R1 AS b ON a.OrderId = b.OrderId;   -- the same view a second timeGO -- vw_R3 joins vw_R2 to itself, vw_R4 then vw_R3, … up to vw_R9CREATE VIEW dbo.vw_R3 ASSELECT a.OrderId, a.D1Name, a.Amount + b.Amount AS AmountFROM dbo.vw_R2 AS aJOIN dbo.vw_R2 AS b ON a.OrderId = b.OrderId;

vw_R3
├── vw_R2 (a)
│   ├── vw_R1 (a)  →  Orders, D1
│   └── vw_R1 (b)  →  Orders, D1
└── vw_R2 (b)
    ├── vw_R1 (a)  →  Orders, D1
    └── vw_R1 (b)  →  Orders, D1

Level 4:     8 × Orders
Level 6:    32 × Orders
Level 9:   256 × Orders

SELECT o.OrderId, d1.Name AS D1Name,       o.Amount * 4 AS Amount   -- 2 to the power of (level − 1), here level 3FROM dbo.Orders AS oJOIN dbo.D1 AS d1 ON o.D1Id = d1.IdWHERE o.OrderId BETWEEN 1000 AND 1100;

The optimizer did not merge the duplicate references, not even with foreign keys. For the same narrow query as above on SQL Server 2022:

With foreign keys, from six levels on every further level cost roughly six to seven times as much compile time. At eight levels, compilation without foreign keys used about 800 MB of memory, and at nine levels with foreign keys about 1 GB. Execution of the narrow query, on the other hand, stayed small because it read only 101 orders. If the query has to read all the data, execution grows too: grouped over all orders, it ran for 70 seconds at eight levels with foreign keys and 212 seconds without. The flat query with the same result took 28 ms.

The limit differed somewhat by version. SQL Server 2019 and 2022 still compiled nine levels with foreign keys, 2025 eight. With the narrow query, SQL Server 2017 hit the 300 seconds already at six levels with foreign keys, although the newer versions compiled the same level in under a second. We did not investigate the reason.

How to proceed

1. Find deep views. The inventory shows nesting depth and the tables behind it. It does not detect views that are used more than once.
2. Get the actual plan of the slow query. Check CompileTime and CompileCPU, StatementOptmEarlyAbortReason, and how many tables the plan reads. If the same table appears many times in the plan although the query logically needs it once, a view has been expanded multiple times.
3. Check foreign keys. Untrusted foreign keys (sys.foreign_keys.is_not_trusted = 1) prevent join elimination. ALTER TABLE … WITH CHECK CHECK CONSTRAINT … makes them checked again; this reads the whole table and belongs in a maintenance window.
4. Flatten the layers. A query that needs only three tables should not go through a view with twelve. For reports, a separate flat view is often worthwhile.
5. Split deliberately where necessary. An intermediate result in a temporary table breaks the optimization into smaller tasks. A CTE or subquery does not: it is expanded just like a view. What staging in TempDB costs is shown in the TempDB hub.

Sources

• Microsoft: Views
• Microsoft: Troubleshoot optimizer timeouts
• Microsoft: Error 8623
• Microsoft: Error 8632
• Microsoft: Disable and re-check foreign key constraints

More on abstraction: T-SQL functions · Back to the overview: The Paradox of Abstraction

More on abstraction