Does SQL Server cache execution plan of a view?

Question

In SQL Server, stored procedures execution plans are cached but view execution plan are never cached. Is that true? If yes, why does SQL Server not cache view execution plans?

If a optimizer is taking a long time to create execution plan, is it helpful to wrap the query in a view?

Seward

It might be that it is not very useful to store the plan of the view because you always need to do an operation on the view in practice. The plan for that operation is cached. That involves a possible transformation of the view query. – Monkeypot

Where did you get this misinformation from? A SELECT on a view is really in the end a select query, and like any other query, it's execution plan will be cached - it's no different from an ad-hoc query or a query from inside a stored procedure ..... a query is a query is a query in SQL Server, and all execution plans are cached (until evicted from cache) – Harrisonharrod

@marc_s: I think the question is about whether the view DDL SQL is cached or not. – Monkeypot

Answer 1

There is no such thing as an execution plan for a view (at best, a parse tree is cached). View is always optimized as a part of the outer query (somewhat simplified, the text of the view is merged with text of the outer query and then optimized).

Will an execution plan of a query using a view be cached or not depends on the very same factors as with any other query.

Azotic

Answer 2

Here are some of my findings

Test Table

CREATE TABLE Test_Table (ID INT  , Value INT)                
GO
INSERT INTO Test_Table 
VALUES 
(1, 100),
(2, 100),(2, 100),
(3, 100),(3, 100),(3, 100)
GO

View on that table

CREATE VIEW vw_Test_View
AS
SELECT ID, SUM(Value) AS Total 
FROM Test_Table
GROUP BY ID
GO

Clear_Chached Plans

-- Clear chache for any chached plans 
-- (Warning do not execute this on Production server)

DBCC FREEPROCCACHE;
GO

Calling View

-- Execute the same select from view twice
SELECT * FROM dbo.vw_Test_View
GO
SELECT * FROM dbo.vw_Test_View
GO

Chached Plans Inspection

-- Inspect Chached execution plans
SELECT UseCounts, Cacheobjtype, Objtype, [TEXT]
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE [TEXT] LIKE '%vw_Test%'
GO

╔═══════════╦═══════════════╦═════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ UseCounts ║ Cacheobjtype  ║ Objtype ║                                                                                                            TEXT                                                                                                            ║
╠═══════════╬═══════════════╬═════════╬════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║         1 ║ Compiled Plan ║ Adhoc   ║ SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan  FROM sys.dm_exec_cached_plans   CROSS APPLY sys.dm_exec_sql_text(plan_handle)  CROSS APPLY sys.dm_exec_query_plan(plan_handle)  WHERE [TEXT] LIKE '%vw_Test%'   ║
║         2 ║ Compiled Plan ║ Adhoc   ║ SELECT * FROM dbo.vw_Test_View                                                                                                                                                                                             ║
╚═══════════╩═══════════════╩═════════╩════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Conclusion

As you can see the execution plan for our SELECT * FROM View was compiled once and reused on second execution of the same query.

It is a view, Plan was chached and it was reused on subsequent execution. Hope this explanation helps . Thank you.

Poison

It seems misleading. The execution play is "select stuff from view" and that is cached. But what about the execution plan for the view itself (which is a different "select stuff from tables")? – Pronounce

@Poison you are wrong. As jcollum say The execution plan for the view itself is not cached. – Quickel

Answer 3

A view is never executed. When it is used as part of a query it is inlined into the query plan as if you had text-pasted its definition. The query optimizer knows nothing about views (except for indexed views).

After inlining of the view definition all the usual caching rules apply. In other words the query is normally cached.

Bade

If we do a SELECT * FROM dbo.VeiwName this will create a plan for this query, and if we execute the same query again will it not reuse the execution plan created for 1st execution? – Poison

The view itself is not executed - true - but any SELECT on a view ultimately results in a regular SELECT query on the underlying table(s) and that SELECT query will have an execution plan and that plan will be cached, just like any other execution plan ..... – Harrisonharrod

@Poison of course it will be cached. Using a view does not make a query special in any way. The usual caching rules apply. – Bade

@Bade if views are inlined into the query when used what is the use of indexing a view? – Spend

@Spend this is a very intelligent question. The optimizer later matches parts of the query to indexed views. It does this regardless of whether you had referenced a view or copy-pasted the view text. It cannot tell the difference. The matching engine works on the inlined query. And yes, this means that sometimes a SELECT * FROM MyView does not use the index! The matching engine is not that good, alas. It starts to fail with many joins or other complex stuff. There's NOEXPAND for that (which is a do-it-yourself solution). – Bade