![]() We should use RECOMPILE option only when the cost of generating a new execution plan is much less then the performance improvement which we got by using RECOMPILE option. This is because of the WITH RECOMPILE option, here each execution of stored procedure generates a new execution plan. When a query is executed, it’s sent to the parser first. This query execution plan is then stored in cache and can be quickly accessed. ![]() Here you see the better execution plan and great improvement in Statistics IO. A compilation is the process when a stored procedure’s query execution plan is optimized, based on the current database and database objects state. A better alternative, on SQL Server 2005 and later, is to use the OPTION (RECOMPILE) query hint on just the statement that suffers from the parameter-sniffing problem. Now execute this stored procedure as: set statistics IO on Using WITH RECOMPILE effectively returns us to SQL Server 2000 behaviour, where the entire stored procedure is recompiled on every execution. Now again creating that stored procedure with RECOMPILE option. Here when we execute stored procedure again it uses the same execution plan with clustered index which is stored in procedure cache, while we know that if it uses non clustered index to retrieve the data here then performance will be fast. Now executing the same procedure with different parameter value: set statistics IO on The output of this execution generates below mention statistics and Execution plan: Select address,name from xtdetails where execute this stored procedure as: set statistics IO on Doing this at execution EXEC procedurename WITH RECOMPILE had no effect. Now create stored procedure as shown below: create procedure as varchar(50)) Recompiling the procedure every time it is run with CREATE PROCEDURE procedurename WITH RECOMPILE slowed the query time. Set into xtdetails table xtdetails contains 10000 rows, where only 10 rows having name = asheesh and address=Moradabad. ![]() Now, I am inserting the data into this table: declare as int Ĭreate clustered index IX_xtdetails_id on xtdetails(id)Ĭreate Nonclustered index IX_xtdetails_address on xtdetails(address) In this case if we reuse the same plan for different values of parameters then performance may degrade.įor Example, create a table xtdetails and create indexes on them and insert some data as shown below: CREATE TABLE. But sometimes plans generation depends on parameter values of stored procedures. If plan found in cache then it reuse that plan that means we save our CPU cycles to generate a new plan. If we again execute the same procedure then before creating a new execution plan sql server search that plan in procedure cache. When we execute stored procedure then sql server create an execution plan for that procedure and stored that plan in procedure cache. Here i am focusing on why we use WITH RECOMPILE option. This executes the procedure and recompiles the procedure's query plan. Some time, we also use WITH RECOMPILE option in stored procedures. To recompile a stored procedure by using the WITH RECOMPILE option Select New Query, then copy and paste the following code example into the query window and select Execute. We use stored procedures in sql server to get the benefit of reusability. Today here, I am explaining the Use of Recompile Clause in SQL Server Stored Procedures.
0 Comments
Leave a Reply. |