Arjun Sivadasan (3/5/2013)
Thanks Gail. For an SP, there can be multiple entries in the dm_exec_query_plan DMV even without SP recompilation is what this implies, right?
No. A procedure has a single plan in cache only (other than when there are set options different)
Will insertion into temp table cause recompilation of SP or only the piece of code where it is queried?
As I said, since SQL 2005, recompilation hasn't been at the procedure level, rather at the statement level so if a statement in a procedure triggers a recompile, it's just the statements that need recompiling that get it.
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