SQL SERVER – Recompile Stored Procedures Two Easy Ways

Recently while working with a large financial organization on Comprehensive Database Performance Health Check, I found an instance where stored procedure had a very incorrect execution plan cache. I told the DBA who was working with me to recompile the stored procedure so we have to brand new plans for the stored procedure. To recompile the stored procedure, DBA opened the stored procedure in SSMS and recreated it by using the alter statement. At that point, I realized that I should have a blog post that explains to easy way to recompile stored procedures.

To recompile a stored procedure you do not have to recreate the stored procedure. Here are two easy methods:

Method 1: WITH RECOMPILE

You can recompile your stored procedure while you execute it. Here is the script.

EXEC StoredProcedureName @parameters WITH RECOMPILE

This method will recompile the stored procedure as soon as the stored procedure executes it.

Method 2: sp_recompile for Recompile Stored Procedures

Here is a neat trick where you can make your stored procedure for recompilation but not actually execute it. Here is the script.

EXEC sp_recompile 'StoredProcedureName'

This method will recompile the stored procedure in the first run of the stored procedure. Essentially this method removes the cache from the procedure cache.

So now you know different methods to recompile stored procedures. One method can be used when you execute the stored procedure and another method that can be used to make stored procedure is ready for recompilation and recompiles during its first run.

Identify the Procedure Cache

Here is the simple script which you can run to identify which stored procedure is cached for your database and various associated properties of the stored procedure.

SELECT SCHEMA_NAME(SCHEMA_ID) SchemaName, name ProcedureName,
last_execution_time LastExecuted,
last_elapsed_time LastElapsedTime,
execution_count ExecutionCount,
cached_time CachedTime
FROM sys.dm_exec_procedure_stats ps JOIN
sys.objects o ON ps.object_id = o.object_id
WHERE ps.database_id = DB_ID();

Reference: Pinal Dave (https://blog.sqlauthority.com)