Check BOL there are quite a few scenarios where query_plan can be null or blank:
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.
When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.
Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. In earlier versions of SQL Server, this condition prevented the query plan from returning and generates error 6335. In SQL Server 2005 Service Pack 2 and later versions, the query_plan column returns NULL. You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format.
There can also be issues if the procedure contains temp tables, or multiple, conditional parts to the procedure: https://blogs.msdn.microsoft.com/psssql/2016/07/13/why-am-i-getting-null-values-for-query_plan-from-sys-dm_exec_query_plan/
Dec 05, 2017 at 09:14 PM
Kev Riley ♦♦