Sp_recompile
Aug 3, 2000
Hello!
SQL Server 7.0
Is it true we don't need to run sp_recompile (all tables) on regular basis
as since SQL Server runs it automatically after UPDATE STATISTICS ....
...and SQL Server runs UPDATE STATISTICS automatically too?
So, SQL Server runs UPDATE STATISTICS automatically then (after that) SQL Server runs sp_recompile automatically ?
BOL says:
Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so.
Thank you.
Anny
View 1 Replies
Apr 12, 2001
Does anyone out there know if a script exists which will recompile all of the stored procedures in a database ? Any suggestions would be appreciated. Thanks
View 1 Replies
View Related
Mar 9, 2000
We have a customer who indicates that a certain process in their financial application consistently fails each morning - after the update statistics and sp_recompile is run. They indicate that after this first failure, they are able to successfully run the process.
Has anyone ever heard of this - where a stored procedure will yield erroneous results when run the first time after an sp_recompile?
Many thanks in advance!
View 1 Replies
View Related
Oct 18, 2007
Folks:
I want to run sp_recompile on a weekly basis. Does anybody have a script / stored procedure which will get all the Stored Procedures and Tables from a database and do a recompile. I wrote this small script but it is giving me a error as "Incorrect syntax near 'sp_recompile'."
CREATE PROCEDURE usp_Recompile
AS
DECLARE @ObjName varchar(255)
DECLARE @Statement nvarchar(255)
DECLARE @Command varchar(255)
DECLARE ObjCursor CURSOR FOR
SELECT name FROM sys.objects
where type in ('P','U')
OPEN ObjCursor
FETCH NEXT FROM ObjCursor INTO @ObjName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'RECOMPILING ' + @ObjName
sp_recompile (@ObjName)
--SET @Statement = 'UPDATE STATISTICS ' + @TableName + ' WITH FULLSCAN'
--EXEC sp_executesql @Statement
FETCH NEXT FROM ObjCursor INTO @ObjName
END
CLOSE ObjCursor
DEALLOCATE ObjCursor
Thanks !!
View 1 Replies
View Related
Nov 29, 2007
UPDATE STATISTICS '
SP_RECOMPILE
i want to verify these 2 statements on the tables in a particular database..
how can i do this..
View 1 Replies
View Related
Jul 13, 2006
Hello,
We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?
Thanks
View 3 Replies
View Related
Aug 30, 2007
I know that all the documentation always tells you that sp_recompile will force a stored procedure to recompile the next time it is executed. However, I am not seeing the recompiles in a SQL Trace, when capturing SP: Recompile events. I have tried this on many different database servers, using sp_recompile and also the WITH RECOMPILE option when creating the proc.
Can anyone explain this?
View 4 Replies
View Related
