SQL Server Performance Forum – Threads Archive
SP:Recompile= ‘ ‘
My procedure recompiles every time a execute it.I know that I shouldn’t use "temp tables". but now it’s too late.
I´ve more the 300000 lines of procedures. Now I have two questions:
1 – how to avoid the recompilation in this cases?
2 – I read this article:http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
and I didn’t find the reason of the recompilation. Neither the profiler ( look at Subject).
— procedure 1
CREATE PROCEDURE dbo.TestRecomp1 AS CREATE TABLE #X (
A INT,
b int
) exec dbo.TestRecomp2 — procedure 2
CREATE PROCEDURE dbo.TestRecomp2 AS select * from #x –test
exec TestRecomp1
tks a lot and
sorry by my poor english
www.sql-server-performance.com/stored_procedures.asp
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp
… to tackle the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Not that I would ever do this, but the recompile is because you added the rows to the table and stats had to update. This will not cause a recompile because the plan doesn’t have to take into account the new rows.
quote:
ALTER PROCEDURE dbo.TestRecomp1 AS CREATE TABLE #x (
A INT,
b int
) exec dbo.TestRecomp2
GO
— procedure 2
ALTER PROCEDURE dbo.TestRecomp2 AS select A,b from #x OPTION(KEEP PLAN) –test
exec dbo.TestRecomp1
ALTER PROCEDURE dbo.TestRecomp1 AS CREATE TABLE #x (
A INT,
b int
) exec dbo.TestRecomp2
GO
— procedure 2
ALTER PROCEDURE dbo.TestRecomp2 AS select A,b from #x OPTION(KEEP PLAN) –test
exec dbo.TestRecomp1
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
still recompiles
using the Option ( KEEP PLAN )
or
Option ( KEEPFIXED PLAN )
I just tried it again. It doesn’t recompile as long as you have the latest service pack no SQL Server and are using SQL Server 2000. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>