quote:

---

The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

Note Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so.

---

quote:

---

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft® SQL Server™ 2000 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).

---

quote:

---

Originally posted by madhivanan

From BOL

quote:

---

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft® SQL Server™ 2000 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).

---

I'm interested to know this
Is it necessary to Recompile even if there is no change in the Table definitions?

Madhivanan

Failing to plan is Planning to fail

---

USE Northwind
GO

CREATE TABLE myLog99(
Sproc varchar(255)
, Log_dt datetime DEFAULT (GetDate())
, spid int
, Duration int
, [SYSTEM_USER] varchar(30))
GO

CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @s datetime, @x int, @y int
SELECT @s = GetDate()
SELECT @x = RIGHT(CONVERT(varchar(25),GETDATE(),121),3)*996, @y = 1
WHILE @y < @x
  SELECT @y = @y + 1
SELECT COUNT(*) FROM Orders
INSERT INTO myLog99(Sproc, spid, Duration, [SYSTEM_USER]) 
SELECT 'mySproc99', @@SPID, DATEDIFF(ms,@s,GetDate()),SYSTEM_USER
SET NOCOUNT OFF
GO

CREATE PROC myDriver99
AS
SET NOCOUNT ON
IF (SELECT DISTINCT Duration 
      FROM myLog99 
     WHERE Duration = (SELECT MAX(Duration) FROM myLog99)) < 1000
	BEGIN
	  PRINT 'WITH RECOMPILE'
	  EXEC mySproc99 WITH RECOMPILE
	END
  ELSE
	BEGIN
	  PRINT 'NO RECOMPILE' 
	  EXEC mySproc99
	END
SET NOCOUNT OFF
GO

EXEC myDriver99
GO
EXEC myDriver99
GO
EXEC myDriver99
GO
EXEC myDriver99
GO

SELECT * FROM myLog99 order by Log_dt DESC
GO

DROP TABLE myLog99
DROP PROC myDriver99, mySproc99
GO

SELECT AVG(Duration) 
FROM 
(
    SELECT TOP 100 Duration 
    FROM myLog99 
    ORDER BY Log_dt DESC
) X

quote:

---

Is there a risk with this strategy that one task that slows the whole server will cause the whole world to re-compile (e.g. the reindex job)? Maybe the average runtime over the last n minutes (or since the last time it was checked), or the average of upper-quartile durations, would be a better threshold?

---