RECOMPILE in SQL Server

Before we get into the Subject we need to understand the definition of RECOMPILE.

From BOL

RECOMPILE Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuse them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. For more information, see
Recompile the Stored Procedure.RECOMPILE is also useful when you create plan guides.

“SQL Server internally decided to use a new plan and generates a new plan based on multiple factors ”

Multiple ways to FORCE RECOMPILE

Passing Parameter Values

CREATE PROCEDURE <Procedure Name> <Parameter>
WITH RECOMPILE AS
BEGIN
……
END

Fixed Parameter Values

CREATE PROCEDURE <Procedure Name> <Parameter @AC_NO INTEGER= NULL>
WITH RECOMPILE AS
    BEGIN
      .....
    END

SQL Statement Query Hint OPTION(RECOMPILE)

As we have learnt what recompile does let’s do a demo to prove what is said. In this demo I am using [AdventureWorks] database.

SQL statement without RECOMPILE (Passed multiple parameter values to check how many plans are generated and cached)

DECLARE @P NUMERIC(10,2) SELECT @P=SUM(LINETOTAL) FROM [SALES].[SALESORDERDETAIL] A JOIN [SALES].[SALESORDERHEADER] B ON A.SALESORDERID=B.SALESORDERID WHERE A.ORDERQTY=1 –-2 --3 GROUP BY A.SALESORDERID

Executing the T-SQL again with the same parameters to check the reuse of plan

Execute the stored procedure or t-sql using Recompile and see if you get the cached plan. The plan will not be cached for next time use.

Leave a Reply

Your email address will not be published. Required fields are marked *