August 21, 2009 at 4:13 am
could you please let me know..what really goes on when we use recompile with create sp
August 21, 2009 at 4:36 am
with recompile means that it will be recompiled on each and every execution of the procedure.
August 21, 2009 at 5:06 am
The proc's execution plan will never be cached and a new one will be generated each time the proc runs. Good for procs that have no single optimal plan or have major parameter sniffing problems, not usually a good idea otherwise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2009 at 6:34 am
Stored Procs have execution plans loaded into memory. Every time a Stored Proc is called, it's precompiled execution plan is used to speed up the time it takes to query data.
When you use the recompile option, you force the database engine to compile a new execution plan every time the Stored Proc is called. You lose the efficiency of having a precompiled plan in memory. However, you usually use this option if your database structure changes frequently.
Chris Fitzgerald 😀
August 21, 2009 at 7:20 am
Chris Fitzgerald (8/21/2009)
However, you usually use this option if your database structure changes frequently.
Not structure. If a table's design changes, all procs dependent on it are automatically marked for recompile. Even with data, when the stats on a table change (as will happen automatically as data changes), all procs based on that table will be marked for recompile.
the With Recompile option is good for procs that, depending on parameters passed, will have very different optimal execution plans. Procs that are prone to parameter sniffing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply