NULL value in query_plan column from the sys.dm_exec_query_plan DMV

Question

I've written a query to return data from the sys.dm_exec_query_plan DMV, but for a specific stored procedure the query_plan column which should contain a link to the XML version of the plan is NULL.

select
    qs.sql_handle
    , qs.statement_start_offset
    , qs.statement_end_offset
    , qs.plan_handle
    , execution_count
    , st.text
    , substring(st.text, (qs.statement_start_offset/2)+1,
        ((case qs.statement_end_offset
            when -1
                then datalength(st.text)
            else
                qs.statement_end_offset
            end - qs.statement_start_offset) / 2 + 1)) as [Filtered text]
    , qp.query_plan
from sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
where st.objectid = object_id('myProcedure')
order by qs.sql_handle
    , execution_count desc

I've checked the documentation and indeed it says that the columns is nullable, but does not say anything about the reasons why its nullable.

Although the procedure has been ran at least two times, my understanding is that the plan cache should have information saved about this stored procedure and its compiled execution plans, but this query returns NULL.

I've also looked in the procedure code to check if it has the RECOMPILE option both at procedure level and at statement level and it doesn't.

My next assumption is that the query_plan column is NULL because all of the queries from the procedure (and from the above result set) are either inserting or reading data from #temporary tables.

Is my assumption correct and is that the reason why no query plan is stored for these queries? Or is there possibly another reason?

Update:

I've checked based on @Shaky's comment if the procedure is encrypted, by verifying the value in sys.dm_exec_sql_text.encrypted and it's 0.

I've also tested my assumption that the procedure plan is not stored because of the use of temporary tables in the queries and for that I created a dummy stored procedure which stored the result in a temporary table and then displayed the data from that table and its execution plan is returned by my query.

NULL value in query_plan column from the sys.dm_exec_query_plan DMV

0 Answers0