sp_recompile <table> does not recompile referencing stored procedures - by Dave Wentzel (live)

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 783842 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/16/2013 12:22:22 PM
Access Restriction Public


According to BOL (http://msdn.microsoft.com/en-us/library/ms181647.aspx), " If object is the name of a table or view, all the stored procedures or triggers that reference the table or view will be recompiled the next time that they are run."  This does not appear to be the case.  In the attached files I create a new database, table (with data), and non-trivial stored proc that references the table.  I execute the procedure and note the plan is cached.  I then run sp_recompile *on the table* and wait a few secs (just in case), and note that the plan is not removed from cache.  I have some other scenarios in there as well.  In the second attached repro script I make one change...I run sp_recompile *proc* instead of table.  In that case everything works as I expect.  
Sign in to post a comment.
Posted by Microsoft on 4/22/2013 at 2:55 PM

The documents here are a little misleading. It is not the case that any plan referencing an object marked with sp_recompile will be evicted.

In this case we mark the underlying object as 'changed', and the next time any plan referencing the object is executed, the engine will detect that a referenced object has changed, and recompile the plan.

In a sense the approach for tables/views is lazy recompile.
The effect however is the same.

We will add some notes to clarify this case on MSDN.