SQL SERVER – Recompile Stored Procedure at Run Time

I recently received an email from reader after reading my previous article on SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning regarding how to recompile any stored procedure at run time. There are multiple ways to do this. If you want your stored procedure to always recompile at run time, you can add the keyword RECOMPILE when you create the stored procedure. Additionally, if the stored procedure has to be recompiled at only one time, in that case, you can add RECOMPILE word one time only and run the SP as well. Let us go over these two options.

The RECOMPILE hint is used with a query and recompiles only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

Option 1:

CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT
*
FROM dbo.tblPerson
WHERE Age >= @MinAge AND Age <= @MaxAge
GO

Option 2:

EXEC dbo.PersonAge 65,70 WITH RECOMPILE

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.

Note: Recompilation is not necessarily the best solution.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – Recompile Stored Procedure at Run Time

  1. Why would someone want to force a recompile every time? The execution plan should really be indicative of a typical call – it’s only rare circumstances where it is incorrect. Perhaps updating statistics or doing some table/index maintenance would be the place to start before you start recompiling your sprocs every time they are called.

    Just a thought.

  2. my name is happy , plz tell me use of stored procedure. becuase query is important about table , then what is use of stored procedure plz define ..

    thankyou

  3. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  4. Pingback: SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31 « SQL Server Journey with SQL Authority

  5. Pinal: Where I work we have a proc that is run in rapid succession (several hundred times) once a day and sometimes it performs really bad.
    We have not been able to identify any reason for this and about 80% of the time a simple sp_recompile does the trick but occasionally we actually have to run an ALTER PROC.

    We’ve monitored db activity with the DBA to identify outside factors but without luck.
    Can you think of an idea why sp_recompile isn’t enough (or anything else that could help)?

    (this is on SQL2000)

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  7. I noticed a strange behavior: I had a SP that called a table-valued function whose execution took 10 seconds.
    After I used WITH RECOMPILE option the execution of the SP took only 1-2 seconds.

    But without this option I tried to compile (right click -> Modify -> Execute) the function before I ran the SP: this led to 1-2 seconds for the SP which was really great. My question was: why did I need to recompile the function before running the SP in order to get better execution time ?

    If I ran the SP directly, it took me 10 seconds.

    Thanks,
    Andrei

  8. Here is a poor excuse to use recompile. I have an app that gets zero results from a proc call. The proc returns rows when executed from a SQL session, but the app can’t always find the result set. SP_recompile fixes the problem for a while, but overnight the condition returns. Until I can arrange a session with the Developer to find out why the App can’t always handle the result set, WITH COMPILE keeps the users off my back every morning.

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