why is this column NULL?sys.DM_exec_query_plan

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  April 1, 2013 at 10:39 pm

  #276001

  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?

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  April 2, 2013 at 12:01 am

  #1602674

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

  Eddie Wuerch
  MCM: SQL

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  April 2, 2013 at 8:32 am

  #1602833

  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