September 6, 2011 at 1:43 am
I am calling a stored procedure from another stored procedure. Initially I noticed that for every execution, a new plan is generated. With profiler SP:recompile, I figured out that it was due to a SELECT query involving temp tables and I used the option of KEEP PLAN to avoid recompilations due to temp table changes.
Now, profiler is not showing any recompilations for the stored procedure but for every execution of the stored procedure, a new plan is being used.
Any idea why query plan is not being reused? Strange thing is profiler doesn't show any signs of recompilation.
Thanks.
September 6, 2011 at 4:55 am
Temporary tables will cause the recompile. If the amount of data you're working with is small, say less than 200 rows, you could switch to table variables. These will not cause recompiles.But, how long is the recompile? If it's not excessive you may see performance benefits from the temp table above & beyond what the recompile will cost you.
Are you sure you need the temp table at all? Frequently they seem to be used as a mechanism to enable row-by-row processing. Avoid that and you avoid the recompiles.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2011 at 5:26 am
praveen_vejandla (9/6/2011)
Now, profiler is not showing any recompilations for the stored procedure but for every execution of the stored procedure, a new plan is being used.
How are you seeing that? Profiler? DMV? Something else?
This might be of interest: http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Temp tables themselves don't cause recompiles in all cases any more. Statistics changes on temp tables still do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2011 at 5:35 am
Are the temp tables causing the recompiles created in the CALLING stored proc ?
That will cause a recompile, you could pass the table a a TVF.
September 7, 2011 at 12:02 am
Through sys.dm_exec_procedure_stats DMV, I figured out that despite using KEEP PLAN hint, there was a new plan generated for every execution i.e. plan was not reused. What is surprising though is with KEEP PLAN hint,
I didn't find any recompilations in profiler trace.
When profiler doesn't show signs of recompilations, naturally we expect that SQL Server might be reusing the existing plan but it is not the case here. A new plan is generated.
September 7, 2011 at 12:32 am
praveen_vejandla (9/7/2011)
When profiler doesn't show signs of recompilations, naturally we expect that SQL Server might be reusing the existing plan but it is not the case here. A new plan is generated.
What tells you that a new plan is getting generated on each call? (The cached time, the execution count? A profiler event?)
http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/
http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Can you post the procedure in question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2011 at 1:44 am
praveen_vejandla (9/6/2011)
I used the option of KEEP PLAN to avoid recompilations due to temp table changes.
Just FYI: KEEP PLAN just changes the recompilation threshold for temporary tables to match the rules for regular tables. You would need KEEPFIXED PLAN to avoid optimality-based recompilations.
This is a great Microsoft technical article on the topic: http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
September 7, 2011 at 3:07 am
>What tells you that a new plan is getting generated on each call?
I could see a new plan handle in sys.dm_exec_procedure_stats. If it uses same plan, use count should increase.
September 7, 2011 at 4:27 am
What does the subtype column on the recompile event in Profiler say ?
September 7, 2011 at 6:16 am
GilaMonster (9/7/2011)
Can you post the procedure in question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2011 at 6:17 am
Also, are you seeing SP:CacheRemove and SP:CacheInsert events in Profiler?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2011 at 10:51 pm
Below is the procedure and this gets called from a top level procedure.
Thanks.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[Iterator] (
@inPk varchar(200)
)
AS
begin
BEGIN TRY
set nocount on
DECLARE @version int
declare @l varchar(20)
select @l = VALUE from DOC where DOC_PK = @inPk
and REQ_KEY = 'UNIT'
set @version=0
IF EXISTS (SELECT 'x' FROM iterator where Worksheet_pk=@inPk)
begin
select @version=MAX(version) from iterator
where worksheet_pk=@inPk
set @version=@version+1
end
else
begin
set @version=1
end
insert into ITERATOR (PK ,PN,PID,BN ,Value1,Value2,CV,SUMMED_VALUE,rc,
dc,version )
select distinct iwrt2.PK ,iwrt2.PN,iwrt2.PID,
tar.BNAME ,iwrt2.VALUE1,iwrt2.VALUE2 ,
iwrt2.VALUE ,iwrt2.SUMMED_VALUE as SUMMED_VALUE,
iwrt2.rsc,iwrt2.dc,@version
from RULEF rf
inner join RULEPT rpty
on rf.rid = rpty.rid
inner join RULEPTS rpt
on rpt.RPID = rpty.RPID
inner join #IWRT iwrt
on iwrt.PID =rf.PID
inner join #IWRT2 iwrt2
on iwrt2.PID =rf.PID
inner join TSAR tar
on tar.TID =iwrt2.TID
and tar.PID =iwrt2.PID
and tar.BNAME =iwrt2.BNAME
if ( @lSU <> 'pu' )
begin
update wi
SET wi.SUMMED_VALUE = wi.SUMMED_VALUE / ISNULL( twhp.cf,1)
from ITERATOR wi
join
#TFWHP twhp
on
wi.PK = twhp.pk
and
wi.PN= twhp.pn
where
wi.pk = @inPk
and wi.version = @version
end
END TRY
BEGIN CATCH
Declare @la nvarchar(4000),
@ErrMessage nvarchar(4000),
@ErrSeverity int
SELECT @ErrMessage = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMessage, @ErrSeverity, 1)
select ERROR_PROCEDURE()
END CATCH
end
GO
September 7, 2011 at 11:40 pm
So its recompiling as the temporary tables are defined 'out of scope'.
Instead of using a temp table you could try passing it as a TVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply