I have a server that over the last week has had to Procedures use bad execution plans when over the last couple years that this machine has been online it has never happened. I am not sure if I am looking at 2 different causes. To make the question quick and short...
When Cross Database Owner Ship chaining is invoked would it cause the execution plans to rebuild when cross database procs are being executed?
I had an application that needed to have the account it used to have its account information changed. (Was using sa, now moved to a specific account). The app needs the database chaining enabled since it has lost SA access. Is there any relation that can be drawn from...
1) I created the new account
I don't see why it would, but the timing is there. I just want to double check before I eliminat this as a possibility.
Testing confirms it. If you change the cross database ownership chain setting on the database it removes the queries from cash. You can test it with this really simple query:
I created a procedure & executed it then changed the settings on the database. It removed the proc from cache. The next time you run the proc it has a new create_time value.
answered Apr 23 '12 at 02:49 PM
Grant Fritchey ♦♦