If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized. When we execute stored procedure then sql server create an execution plan for that procedure and stored that plan in procedure cache. Youll want to use OPTION (RECOMPILE) on the statement (s) with issues, not WITH RECOMPILE on the procedure. Here i am focusing on why we use WITH RECOMPILE option. Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others. Some time, we also use WITH RECOMPILE option in stored procedures. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. Recompile a Stored Procedure Before You Begin. We use stored procedures in sql server to get the benefit of reusability. In this situation, it can be useful to force the stored procedure to recompile the next time it executesĪnother reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure. CLR integration does not apply to Azure SQL Database. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. NET Framework CLR into SQL Server is discussed in this topic. Your requirement is like asking how to define a query where the FROM is defined before the SELECT. Should ruby-plsql be recompiling database code that has been left in an invalid state (Eg a related table changes etc). It also occurs if an underlying table used by the stored procedure changes. As per the syntax documentation the WITH clause, and the procedure options (with includes RECOMPILE ), goes before the AS. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. The generated query plan doesn't replace a plan stored in cache when the same query runs without the RECOMPILE hint. "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. Instructs the SQL Server Database Engine to generate a new, temporary plan for the query and immediately discard that plan after the query completes execution.
0 Comments
Leave a Reply. |