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.

SQL SERVER - Recompile Stored Procedures Two Easy Ways recompile-stored-procedure-800x158

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)

Quest

SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER 2019 – Supports Compatibility Level from 2008 to 2019
Next Post
SQL SERVER Management Studio – Enable Statistics Time and IO for Every Query

Related Posts

2 Comments. Leave new

  • you can also add WITH RECOMPILE in your stored procedure creation statement, such as:

    create procedure your_sp
    WITH RECOMPILE
    as

    Reply
  • brian beuning
    July 16, 2023 6:34 am

    If one SP has multiple plans in the cache, will sp_recompile remove all of them
    or only the one I would use? I guess I should try it.

    Reply

Leave a Reply