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

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

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.

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

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?

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

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?

Nope, I haven't. As I said in my previous post, the way you have the code broken up in your post I don't know what code to run as a single batch.

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

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?

Nope, I haven't. As I said in my previous post, the way you have the code broken up in your post I don't know what code to run as a single batch.

Care to elucidate on this?

i) First run the script to create the sproc from first block.

ii) Execute the entire script from second block.

iii) run the third script to make sure you see the execution plan.

iv) now open another query window,run the script from second block.

v) again run the script from third block, you will see another exec plan created.

I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.

Lynn Pettis (4/10/2013)I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.

Yup i totally get that. However if you carefully look at the sproc we are not even hitting the condition i.e If Debug=1 because we are executing the sproc as exec dbo.Test_Sproc @Debug=0 , why would it matter what is inside the If condition if we are not even hitting that condition?

Lynn Pettis (4/10/2013)I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.

Edit: Actually the temporary table #test is different when created from different sessions.

Yup i totally get that. However if you carefully look at the sproc we are not even hitting the condition i.e If Debug=1 because we are executing the sproc as exec dbo.Test_Sproc @Debug=0 , why would it matter what is inside the If condition if we are not even hitting that condition?