In SQL Server there is no right or wrong answer unless while using production we are facing the negative consequence of it. While many want to argue that this may be very bad advice, there are moments where it has been amazing advice as well. There is only one sure resolution of the performance issue if the SQL Server execution plan is building execution plan which is not efficient for the parameter passed and that is the Recompilation of the stored procedure at every single run with the query hint OPTION (RECOMPILE). It is quite possible that even though we use the average value due to statistics the performance problem has yet not resolved. There is no sure solution to overcome the problem if your stored procedure has sniffed the parameter and used that to build the execution plan. However, it is extremely difficult to overcome the performance problem which is introduced due to the parameter sniffing issue. It is extremely easy to overcome the problem of parameter sniffing.
#SQL SERVER RECOMPILE STORED PROCEDURE SERIES#
This post summarizes the entire series of parameter sniffing, performance and recompiling query. Performance and Recompiling Query – Summary.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.
SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE).This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
Today is the last blog post of the year 2019 and this is also a final blog post in the series of Parameter Sniffing which I have been writing.
0 kommentar(er)
