Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,500 database tech articles on his blog at. Pinal has authored 13 SQL Server database books and 40 Pluralsight courses. He holds a Masters of Science degree and numerous database certifications. Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 17 years of hands-on experience. Note: Recompilation is not necessarily the best solution. This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.ĬREATE PROCEDURE dbo.PersonAge INT, INT)
The RECOMPILE hint is used with a query and recompiles only that particular query. Additionally, if the stored procedure has to be recompiled at only one time, in that case, you can add RECOMPILE word one time only and run the SP as well. If you want your stored procedure to always recompile at run time, you can add the keyword RECOMPILE when you create the stored procedure.
#With recompile stored procedure how to
I recently received an email from reader after reading my previous article on SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning regarding how to recompile any stored procedure at run time.
0 kommentar(er)
