Home Dashboard Directory Help
Search

sp_recompile <table> does not recompile referencing stored procedures by daveadfadfadf


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 783842
Opened: 4/16/2013 12:22:22 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft on 4/22/2013 at 2:55 PM
Hello,

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.

Thanks!
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
recompile at the proc level.sql 4/16/2013 3 KB
recompile at the table level.sql 4/16/2013 3 KB