AdHoc usecount

Last Post 26 Jan 2013 03:39 PM by rerichards. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
rerichards
New Member
New Member

--
24 Jan 2013 06:33 AM
I am trying to understand adhoc.

It seems like I should see the plan getting reused if I run the following statement repeatedly in a WHILE loop. However, I do not see the UseCounts ever going above one.

WHILE 1 = 1
SELECT LKey, ScheduleFID, CodeFID, Amount, Display, ChangedAt, ChangedBy
FROM dbo.vw_mf_Schedule
WHERE ( ChangedAt > '1/20/2013 12:00:00 AM' )

This is the DMV I am using for evaluation:

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,
cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype = 'adhoc'
AND st.text like '%vw_mf_Schedule%'

Can someone explain to me why I do not see reuse ever exceed one, when the statement is being executed in the WHILE loop?
gunneyk
New Member
New Member

--
25 Jan 2013 12:22 PM
It could be considered a trivial plan in which case it won't get cached at all. If you omit the part of the WHERE clause that filters on objtype do you still see it in the cache? Have a look at this white paper for more details on how the cache works and what a trivial plan is:

http://msdn.microsoft.com/en-us/lib....100).aspx
rerichards
New Member
New Member

--
25 Jan 2013 07:11 PM
That was a good thought. So I looked in the XML output of the query plan and it listed the following: StatementOptmLevel="FULL".

Still trying to understand this behavior...
gunneyk
New Member
New Member

--
26 Jan 2013 08:02 AM Accepted Answer
OK what you are looking at is the stub of the plan that gets used to find a match. However there is the full plan that gets the usecounts bumped up. Remove the filter "WHERE cp.objtype = 'adhoc' " and just look for the text and you should see what I am talking about.
rerichards
New Member
New Member

--
26 Jan 2013 03:39 PM
Thanks gunneyk. Spot on. I am now seeing the use counts for this statement.
You are not authorized to post a reply.

Acceptable Use Policy