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