September 28, 2016 at 12:18 pm
Comments posted to this topic are about the item Recompile All Databases stored Procedures
October 13, 2016 at 9:04 am
This could be useful as we have a need to do this on occasion.
October 13, 2016 at 11:07 pm
1. Should we mark all procedures for recompilation, when upgrading to a new server version?
2. When the database is changed, like adding indexes or changing data in indexed columns, is the procedures automatic marked for recompilation?
This script come in handy
October 14, 2016 at 2:04 am
The script is not work when the Stored Procedure are in a specific schema.
USE [DatabaseName] EXEC sp_recompile [Schema.Storedprocedure]
INSERT INTO @TblTable
(
DBName
, RecompileStmt
)
VALUES
(
@name
, N'Select N' + CHAR(39) + 'USE [' + @name
+ '] EXEC sp_recompile ['+ CHAR(39)+'+SPECIFIC_SCHEMA+' +CHAR(39)+'.' + CHAR(39)
+ '+SPECIFIC_NAME+' + CHAR(39) + ']' + CHAR(39)
+ ' from [' + @name
+ '].INFORMATION_SCHEMA.ROUTINES where routine_type = '
+ CHAR(39) + 'PROCEDURE' + CHAR(39)
);
October 14, 2016 at 4:17 am
Based on this excerpt from MSDN the SP_Recompile will just clear the plan cache for the associated object.
The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.
(From https://msdn.microsoft.com/en-us/library/ms190439(v=sql.110).aspx)
This means you've essentially developed a script that replicates what DBCC FREEPROCACHE does.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy