why is this column NULL?sys.DM_exec_query_plan

  • I am using below SQL to get actual execution plan:

    select * from(

    SELECT cp.objtype AS PlanType,

    Object_name(st.objectid, st.dbid) AS ObjectName,

    cp.refcounts AS ReferenceCounts,

    cp.usecounts AS UseCounts,

    st.TEXT AS SQLBatch,

    qp.query_plan AS QueryPlan

    FROM sys.dm_exec_cached_plans AS cp

    CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) AS qp

    CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) AS st

    ) a

    where a.ObjectName='My_storedprocedure'

    From BOl;

    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.

    Server has enough memory, how come the plan is "evicted" in my case? There is literally nothing running on the server when i ran my sproc?

  • If you specified WITH RECOMPILE in the CREATE PROC/ALTER PROC statement, the plan won't be cached.

    Eddie Wuerch
    MCM: SQL

  • Eddie Wuerch (4/2/2013)


    If you specified WITH RECOMPILE in the CREATE PROC/ALTER PROC statement, the plan won't be cached.

    I didn't specify any query hints

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply