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)
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.
Good Morning Champ, the link given in first paragraph is not working.
Thanks Ritesh,
It is fixed now!
Hey, Thanks for article but the link given does not word
Neither does your grammer ;-)
word = work?
Grammer = grammer… I’m fired.
Link is fixed!
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
plz gys tell me the procedure about how to generate the stored procedure at runtime of all tables.
will be thankful to you………
You need to give more informations on what you want to acheive
[...] 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. (Read more here) [...]
[...] Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time Recompile Stored Procedure at Run Time Encrypted Stored Procedure and Activity Monitor Stored Procedure and [...]
Refer this – http://www.varindersandhu.in/2011/11/15/sql-server-stored-procedure-with-recompile/
thanks for article.
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)
[...] Recompile Stored Procedure at Run Time 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. [...]
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