curious_sqldba (4/10/2013)

---

Lynn Pettis (4/10/2013)

---

curious_sqldba (4/10/2013)

---

I have been battling for weeks on this issue, i was able to resolve the issue but i don't the exact root cause. I am posting complete code, would like to know feedback from others. Issue is that a new execution plan is created from scratch for every execution. Temp table created outside the scope of the sproc is causing the issue, but what i dont get is there is section of code which doesn't even satisfy the condition but seems like is compiled. Please execute the entire script to get better idea.

Sample Sproc

CREATE procedure dbo.Test_Sproc @Debug BIT = 0

as

select getdate()

if @Debug =1

begin

select * from #test

end

create table #test (name nvarchar(max))

insert into #test values( 'John')

exec dbo.Test_Sproc @Debug=0 -- notice i am passing 0 and not 1 , so the block of code shouldn't be executed

drop table #test

Check exec 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,

Cp.Plan_Handle As PlanHandle

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='Test_Sproc'

Now you should see one exec plan, re-run the entire batch and check the number of execution plans, you should be seeing two. Any idea why?

The way everything is posted it is hard to tell what makes up the complete script. A guess I have is that there is no GO after the END that you "think" marks the end of the stored procedure but actually doesn't.

You can add a GO there, this is just a example. My intent here was to depict a strange scenario where execution plans are not being cached. Did you try executing the code?