Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
lol they are optimizing the queries finally! –  user547 Jan 21 '11 at 19:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.