x

Cross Database Ownership Chaining

Hey everyone,

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
2) Moved the app from SA over to new account
3) Determined that chaining needed to be enabled
4) Enabled the chaining job
5) Now the app has the ability to call a proc that calls a nested proc in a different database. The permissions work fine, but would a new execution plan be needed?

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.
more ▼

asked Apr 20 '12 at 05:03 PM in Default

Chris shaw gravatar image

Chris shaw
530 2 3 5

I'll need to test it, but I think the answer is yes. Setting up several other tests at the moment. I'll get to this on ASAP
Apr 23 '12 at 11:12 AM Grant Fritchey ♦♦

Thanks,

That is what I thought, but I wanted to get another opinion before I sent that along as an answer.

Apr 23 '12 at 03:19 PM Chris shaw
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Yes.

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:

SELECT deqs.creation_time 
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE dest.text LIKE 'CREATE PROCEDURE x%'
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.
more ▼

answered Apr 23 '12 at 02:49 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x241
x158
x38

asked: Apr 20 '12 at 05:03 PM

Seen: 919 times

Last Updated: Apr 23 '12 at 03:19 PM