SQL Server 2008 - Cross database performance on same physical machine and server instance

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

You should not see performance degredation when querying across databases on the same instance. The same query analyzer, buffers, etc. are in use.

Now, if you were to attempt the same across distinct SQL instances on the same server you could see a performance impact. The query would have to be analyzed by two distinct query analyzers and more system resources would be used for caches, buffers, etc. You would also force results to go through the VIA protocol (assuming it's enabled) between the instances. MS DTC could be involved, and if permissions are inadequate on one of the instances a poor(er) query plan may be created.

Answer 2

the Profiler + execution plan + IO/Cpu statistics are your best friend at checking this. Copy your table in the first db and compare duration and the other statistics with the ones found when having the table in the second db. You will see if there are any penalties.

I personally didn't see a visible performance hit when reading tables data from other databases on the same server.