patternModerate
SQL Server 2008 - Cross database performance on same physical machine and server instance
Viewed 0 times
same2008sqlcrossanddatabaseinstanceperformancephysicalmachine
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#692, answer score: 15
Revisions (0)
No revisions yet.