Recompile a Stored Procedure SQL server | An Easy Method

Today let’s have a look at how to Recompile a Stored Procedure SQL server method by our MSSQL support services at Bobcares.

Recompile a Stored Procedure SQL Server Overview

SQL stored procedure:

A stored procedure is a ready T-SQL code that can be reused again and again and the most essential benefits of using stored procedures are listed below:

Performance: After the very first execution of the stored procedure, the query optimizer creates an execution plan that will be stored in the query plan cache. Hence, all other next tasks of the same SQL Server stored procedure will use this cached stored procedure. This method aims to avoid unwanted query compilation processes.

Code reuse: This helps to avoid rewriting the same codes again and again.

Maintainability: Using stored procedures simplifies code maintenance, and has wide usage in different applications but only changing the stored procedure will easily affect all applications.

Security: Help us to get rid of dealing with the security settings of different database objects, it is enough to give only permission to the stored procedure.

Recompile Stored Procedures in Two Easy Ways:

To recompile you need not recreate the stored procedure. Here are two easy methods:

Method 1: WITH RECOMPILE

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

EXECCopy Code

StoredProcedureName @parameters Copy Code

WITHCopy Code

RECOMPILECopy Code

This will recompile the stored procedure as soon as it performs the task.

Method 2: sp_recompile for Recompile Stored Procedures

Here you can make your stored procedure for recompilation, Here is the script.

This technique will recompile in the first run of the stored procedure, it removes the cache from the procedure cache.

The first method is used when you execute the stored procedure and another method is used to make the stored procedure ready for recompilation and recompiles during its first run.

Identify the Procedure Cache

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

SELECTCopy Code

SCHEMA_NAME(SCHEMA_ID) SchemaName, Copy Code

nameCopy Code

ProcedureName,Copy Code

last_execution_time LastExecuted,Copy Code

last_elapsed_time LastElapsedTime,Copy Code

execution_count ExecutionCount,Copy Code

cached_time CachedTimeCopy Code

FROMCopy Code

sys.dm_exec_procedure_stats ps Copy Code

JOINCopy Code

sys.objects o Copy Code

ONCopy Code

ps.object_id = o.object_idCopy Code

WHERECopy Code

ps.database_id = DB_ID();Copy Code

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, we have seen Recompile a Stored Procedure SQL server and its two options with details.  By using the “WITH RECOMPILE” and “sp_recompile for Recompile Stored Procedures” methods.