What is Parse Tree cache object type in sql server

Question

When we query SYS.DM_EXEC_CACHED_PLANS there is a cache object type called parse tree showing up for views and table valued functions. Is it similar to the query plan for stored procedures and ad hoc queries?

Answer 1

No.

This is the output from an earlier stage in the process. It gets substituted into queries that reference Views before the compilation stage proper. The stages involved are

• Parsing (Output: Parse Tree)
• Binding (Output: Algebrized Tree)
• Query Optimisation (Output: Execution Plan)
• Query Execution

For a full description of these see Benjamin Nevarez's article The SQL Server Query Optimizer

AFAIK despite showing up as Parse Tree in sys.dm_exec_cached_plans it is actually the output from the second stage that is stored and substituted into queries. This is supported from this query in which these objects show up as Bound Trees

SELECT text, 
       objtype, 
       refcounts, 
       usecounts, 
       size_in_bytes, 
       cacheobjtype, 
       name 
FROM   sys.dm_exec_cached_plans p 
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
       JOIN sys.dm_os_memory_cache_entries e 
         ON p.memory_object_address = e.memory_object_address 
WHERE  cacheobjtype = 'Parse Tree'