Sp_recompile - Script ???
Apr 12, 2001Does 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
ADVERTISEMENT
Sp_recompile
Aug 3, 2000Hello!
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
Sp_recompile Errors
Mar 9, 2000We 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!
Sp_recompile Script/stored Procedure
Oct 18, 2007Folks:
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 !!
How To Verify Sp_recompile On Tables In A Database
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..
Merge Agent System Stored Procedures And Sp_Recompile
Jul 13, 2006Hello,
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
No Recompile After Sp_recompile Or WITH RECOMPILE Option
Aug 30, 2007I 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?