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 (https://blog.sqlauthority.com)
20 Comments. Leave new
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.
Hey, Thanks for article but the link given does not word
Neither does your grammer ;-)
word = work?
Lol, internet nazi, its grammar.
Grammer = grammer… I’m fired.
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
Refer this –
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)
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
Sir,
Can you please advice me in which situations we have to use RECOMPILE ?
Thanks,
Mansi
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.
If we are RECOMPILE a procedure every time when we execute through application,Does it affect the performance?
Nelson – It may not be a very noticeable change but remember that recompilation consumes CPU cycles. If you have a LOTs of connection running same procedure, CPU might be a contention.
hi Andrei,
For the same situation i have faced now, any solution for this