Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: Effects of using 'WITH RECOMPILE' in proc definition?

My understanding of the WITH RECOMPILE option with stored procedures is generally limited to using the clause with a single stored proc call as a trailing parameter:

exec sp_mystoredproc 'Parameter1', 2, '1/28/2011' with recompile

What are the effects of including WITH RECOMPILE in the actual proc definition? Does this recompile the proc every time it's executed? Or just the next time the proc is altered?

Example:

CREATE PROCEDURE [dbo].[sp_mystoredproc]
    (@string1           varchar(8000)
    ,@int2              int = 2
    ,@dt_begin          DATETIME
    with recompile
AS
... proc code ...
like image 737
Dan Esparza Avatar asked Jan 28 '11 17:01

Dan Esparza


People also ask

What is the use of with recompile statement in SQL Server?

To recompile a stored procedure by using sp_recompile This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.

What does SP recompile do?

Remarks. sp_recompile looks for an object in the current database only. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled.

What happens when a stored procedure is compiled?

Stored Procedure is nothing but a precompiled SQL statement. This means that once we compile a query, it stores the result and the next time we don't need to compile it again and again. It means it is prepared SQL code that you can save and reuse later multiple times.

What is recompile stored procedure in SQL?

What is the RECOMPILE option? The compilation is the process when a query execution plan of a stored procedure is optimized based on the current database objects state. This query execution plan is often stored in the cache to be quickly accessed. Recompilation is the same process as a compilation, just executed again.


1 Answers

This makes the proc rebuild the plans of all queries every time it's run.

Useful it the values of the proc parameters affect the filter selectivity.

Say, the optimal plan for this query:

SELECT  *
FROM    orders
WHERE   order_date BETWEEN @begin_report AND @from_report

will be a full scan if the date range is large or an index scan if it's small.

Created using WITH RECOMPILE, the proc will build the plan on each execution; without one, it will stick to a single plan (but will save time on recompilation itself).

This hint is usually used in procs processing large volumes of data and doing complex reports, when the overall query time is large and time for rebuilding the plan is negligible compared with the time saved by a better plan.

like image 85
Quassnoi Avatar answered Sep 23 '22 13:09

Quassnoi