SQl 2008 cross database performance on same physic

Question

Is there any performance hit when doing a select across another DB on the same physical machine? So I have 2 databases on the same physical machine running within the same SQL 2008 instance.

For instance in SomStoreProc on_this_db I run

SELECT someFields FROM the_other_db.dbo.someTable

So far from what I have read on the internet, most people seem to indicate NO.

Answer 1

Even if it is not a performance hit, it could be a problem in data integrity as FKs can't be enforced across databases.

However, it is more likely your procs need to be tuned especially if they are thousands of lines long. To begin with look for cursors, correlated subqueries and bad indexing. Also look for where clauses that are nonsaragable and scalar functions that are runing row-by-agonizing-row.

Of course the best way to prove that the separate database is not the issue is to take one slow proc and convert those tables to one database and test performance both ways. Please at least convince them to do this smaller test before they go ahead and make the horribly complicated and time consuming change to one database and then find out they still have performance problems.

And remember, the execution plan is your friend is looking at these things.