Question: How to Recompile Stored Procedure?
Answer: The answer to this question is here:
EXEC sp_recompile 'NameofSP'
Well, that’s it technically we answered the question which was asked to us. However, the real question one should ask is when do we actually need to recompile the stored procedure. I only see one reason when one has to recompile the stored procedure is when the SP is facing the parameter sniffing issue and due to the issue, it is giving us poor performance.
Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.
- SQL SERVER – Parameter Sniffing Simplest Example
In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure. - SQL SERVER – Parameter Sniffing and Local Variable in SP
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. - SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed. - SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing. - SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance. - Performance and Recompiling Query – Summary
This post summarizes the entire series of parameter sniffing, performance and recompiling query.
I suggest you go through the blog posts and understand what is parameter sniffing and how you can overcome it by using various different solutions.
Reference: Pinal Dave (https://blog.sqlauthority.com)
