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


ADVERTISEMENT

Sp_recompile - Script ???

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

Sp_recompile Errors

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

Sp_recompile Script/stored Procedure

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

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..

View 1 Replies View Related

Merge Agent System Stored Procedures And Sp_Recompile

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

No Recompile After Sp_recompile Or WITH RECOMPILE Option

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







Copyrights 2005-15 www.BigResource.com, All rights reserved