Home > Uncategorized > RECOMPILE in SQL Server

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 a 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

  1. Passing Parameter Values
    CREATE PROCEDURE <Procedure Name> <Parameter>
    WITH RECOMPILEAS

    BEGIN

    ……

    END

  2. Fixed Parameter Values

    CREATE PROCEDURE <Procedure Name> <Parameter @AC_NO INTEGER= NULL>

    WITH RECOMPILE

    AS

    BEGIN

    ……

    END

  3. SQL Statement Query HintOPTION(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(LINETOTALFROM [SALES].[SALESORDERDETAIL] A
    JOIN [SALES].[SALESORDERHEADER] B
    ON A.SALESORDERID=B.SALESORDERID WHERE A.ORDERQTY=1 –-2 –3
    GROUP BYA.SALESORDERID


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

Execute the SP or T-SQL using Recompile and see if you get the cached plan. The plan will not be cached for next time use.

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: