Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

why is this column NULL?sys.DM_exec_query_plan Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 10:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 1,274, Visits: 2,938
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?
Post #1437687
Posted Tuesday, April 2, 2013 12:01 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:18 PM
Points: 442, Visits: 1,124
If you specified WITH RECOMPILE in the CREATE PROC/ALTER PROC statement, the plan won't be cached.


Eddie Wuerch
MCM: SQL
Post #1437709
Posted Tuesday, April 2, 2013 8:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 1,274, Visits: 2,938
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
Post #1437934
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse