Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2006-03-07 : 13:27:23
|
| The BOL entry for sp_recompile states that using it on a sproc will cause the sproc to be recompiled the next time it is run. It also says using sp_recompile on a table will cause all sprocs that reference that table to be recompiled the next time they are run.So, I run SprocA and it shows up in syscacheobjects. I then do "sp_recompile SprocA" and it is removed from syscacheobjects. Ok, that makes sense - its not in the cache now so the next time it is run it will need to be recompiled.However, if I do "sp_recompile TableA" where TableA is referenced inside SprocA, SprocA does not get removed from syscacheobjects at all. Based on the BOL entry I expected SprocA's plan to be flushed. Why doesn't this happen? Continued executions of SprocA increases the usecounts column, so I know the old plan is still being used.It appears in my testing that using a table with sp_recompile doesn't tag the referencing sprocs for recompile like BOL says. Is that true, or am I missing something?Thanks.--3P |
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-03-09 : 13:25:36
|
| Please post here, incase you figured this out.ThanksSree |
 |
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2006-03-13 : 09:12:30
|
quote:
Originally posted by kpsreenath
Please post here, incase you figured this out.
I certainly will, but I haven't figured it out yet, and no one else seems to know...  -- 3P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-13 : 13:30:15
|
| Is it possible your SYSDEPENDS is not up-to-date, and thus didn't detect that your Sproc is dependent on TableA?If so might be worth recreating your SprocA and then repeating the experiment.Kristen |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2006-03-16 : 11:44:21
|
quote:
Originally posted by Kristen
Is it possible your SYSDEPENDS is not up-to-date, and thus didn't detect that your Sproc is dependent on TableA?If so might be worth recreating your SprocA and then repeating the experiment.
Good idea, but no go. I recreated the sproc, double-checked sysdepends, and reran the test with no change in the results. The plan doesn't get flushed when the referenced table gets recompiled.  Oh well.Jerry |
|
|
|
|
|
|
|
|
sp_recompile object and syscacheobjects