Why dont I see execution plan sometime using exec Query Plan

PRO 2014-05-13 20:57:24

Hi Experts,

When I run sys.dm_db_exec_query_plan(Plan_handle), why dont I see query plan always? It shows NULL.

@SQLTrooper 2014-05-13 21:02:19
Without seeing your query – this is where we might start:

http://technet.microsoft.com/en-us/library/ms189747(v=sql.105).aspx

Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.

Some Transact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

If a Transact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

SQLkiwi 2014-05-13 22:11:11
The most common reason I see for this is that the query plan xml contains more than 128 levels of nesting.

Try sys.dm_exec_text_query_plan instead; it returns nvarchar(max), not xml. As a bonus, you can also extract single plans within a batch using this DMV. Once you have the part of the plan you need you can always try casting it to xml if that is what you need.