Menu
What does it mean to "recompile" a stored procedure on a SQL Server? Should this be a manual process or an automated one? What are the pros and cons of this process? Thank you!
546
When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes.
Stored Procedure is nothing but a precompiled SQL statement. This means that once we compile a query, it stores the result and the next time we don't need to compile it again and again. It means it is prepared SQL code that you can save and reuse later multiple times.
A recompilation is the same process as a compilation, just executed again. If the database structure or data change significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.
Use the ALTER PROCEDURE statement to explicitly recompile a standalone stored procedure. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.
It's strictly an internal operation within SQL Server.
As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them
Here's MSDN's article on Recompiling Stored Procedures
When you issue an ALTER PROC statement, you're also causing a recompile of the stored proc.
113
Use of Recompile makes SQL Server to recompile to SP, hence instead of using existing plan for same SP, Database engine will create new execution plan.
It can be both Automated and Manual process depending upon your requirement. You can make SP recompile every time when it get executed by including RECOMPILE "hint" in your SP. Or run
Exec sp_recompile N'yourSP'
to manually recompile.
And as @Michael mentioned, too many recompilations can be very bad since for each compilation database engine has to create new execution plan which could be costly operation. So generally automatic recompilation of SP should be avoided, and even manually recompilation should be tested in development environment first before using it production to be sure that it really does help.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
