October 28, 2011 at 1:11 am
[font="Verdana"]SQL Server tries best to maintain execution plans of objects as to maximum extent possible, but in case why we need to recompile a stored procedure each time? or in which conditions?
We had gone trough from such problem, in which a procedure was called iteratively after the interval of 1-2 minuets, and was unable to respond in some specific iteration. Then we need to manually recompile (SP_RECOMPILE) the procedure with ad-hoc interference and as result the procedure healed enough to produce output!
Secondly, to embed RECOMPILE within procedure's definition would not add up to recompilation overhead with each iteration?
Thanks!
[/font]
October 28, 2011 at 2:44 am
Abrar Ahmad_ (10/28/2011)
SQL Server tries best to maintain execution plans of objects as to maximum extent possible, but in case why we need to recompile a stored procedure each time? or in which conditions?
Typically for queries where the optimal execution plan varies dramatically depending on parameter values
Secondly, to embed RECOMPILE within procedure's definition would not add up to recompilation overhead with each iteration?
Sure.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply