1

uses sp_blitzcache by Brent Ozar

SQL SERVER 2012 (11.0.6020.0)

Steps to reproduce:

create table b (b int)
create table #b (b int)

create procedure Y as
begin
insert into b select 1
select * from b
insert into #b select 1
select * from #b
end

open two query windows in MS SSMS. run

checkpoint
go
dbcc freeproccache
go

in both of them (just to be sure) run sp_blitzcache this will return empty (ok)

run procedure Y in both few times. run sp_blitzcache As can be seen, the procedure has a plan compiled for each connection separately

My question is:

Is there any way around? For the same stored procedure to use the same plan, even trogh the (right now this causes over 80% recompile)

  • It is likely getting recomped because of the temp table. you can verify by running a profiler trace / extended event session and capture recomplies, it will give you the reason. – Ryan Cooper Nov 11 '16 at 19:15
  • @RyanCooper Yes, it IS recomped because of the temp table. That i know now of FACT. What i am looking for right now are accepted solutions, how-tos and patterns to solve this... – Vladislav Zalesak Nov 11 '16 at 20:27
  • I would test using OPTION (RECOMPILE) on your queries that reference the temp table. It is possible this could force only a statement-level recomp rather than the entire procedure. – Ryan Cooper Nov 11 '16 at 21:34
  • Using OPTION(KEEP PLAN) may help here, since the recompile is specifically for the temp table. – Erik Darling Nov 12 '16 at 0:58
  • 1
    Okay, what I would do is run profiler or XE and capture the reason for the recompile. – Erik Darling Nov 12 '16 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.