15

Are cross-database queries expensive in SQL Server? All of the databases are in the same instance.

  • 3
    I have never found any evidence that they are, no. The only exceptions I could even think of (but haven't tested) are (a) that the optimizer can't benefit from relationships that can't be explicit (e.g. it can benefit from foreign key definitions, that can't exist cross-database) or (b) there are issues with statistics being a problem over linked servers, but I imagine there may be cases where even across database a user running a query can't see stats. Don't know if (b) is real or not - haven't tested, just know that can be a serious issue across servers. – Aaron Bertrand Apr 25 '14 at 18:48
  • 2
    I'm with @AaronBertrand on this one, I haven't seen anything to definitively say there's a huge hit. The only thing I could think would add something to the equation would be possibly some form of authentication operations, but I would doubt it would be significant. That's just me thinking out loud, I'm not sure. Your best bet here would be to set up an automated test/benchmark with a cross-database, then with the same query inter-database and run that test thousands of times. Get the average, max, etc. and make your decision based off of that. – Thomas Stringer Apr 25 '14 at 19:10
  • 1
    @thomas could you see a difference definitively by looking at the actual query plans? – Max Vernon Apr 26 '14 at 0:51
  • @Jonathan: the definition of "expensive" is relative to alternatives. So, what are you trying to do and what are you deciding between? Without more info it is quite possible to get an answer that appears correct to this question at face-value, but at the same time is absolutely wrong for your situation. Everything has a cost, but that cost could be either cheap or expensive depending on what you are comparing it to. And query costs (i.e. time, contention, etc) need to be weighed against maintenance costs, hardware costs, etc. – Solomon Rutzky Apr 27 '14 at 1:50
  • I would like to know the cost in comparison to a query in the same table. – Jonathan Allen Apr 27 '14 at 2:51
6

Sorry, I don't have enough reputation to comment on the question, but from my experience if the client application raises a transaction for a query which uses cross database joins then it would promote the transaction to distributed and have the overhead of a DTC transaction.

The DTC overhead in this case could be viewed as a negative to performance. Generally the difference would be negligible although Microsoft describe DTC transactions as such:

Distributed transactions typically consume significant system resources

Transaction Promotion

...which would suggest a performance degradation if your server cannot offer the resources it requires.

Just to clarify, the article above describes local transactions being promoted when remote systems are introduced, but I have seen this become the case for transactions on the same server when using cross database queries.

As Thomas Stringer points out in his comment, there will be extra overhead in authentication although I think as this will be SID-driven there will be minimal overhead there unless you have to use separate credentials to access the other database.

If there were difference in database settings which caused additional overhead in the join that could impact larger than the previous suggestions - for example database collation. Database collation could manifest as a functional difference, not just a performance difference.

I think Aaron has the strongest argument for performance with the optimizer not having the advantage of using relationships for cross-database queries whereas self-contained within a database you could use relationships to your advantage.

-2

In SQL Server 2014, memory-optimized tables do not support cross-database transactions. You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. You cannot easily copy data from a table in one database, to a memory-optimized table in another database.http://msdn.microsoft.com/en-us/library/dn584627(v=sql.120).aspx Distributed and cross-database transactions are not supported for database mirroring/AOAG. http://technet.microsoft.com/en-us/library/hh393530.aspx. If you are going to use ownership chaining, be aware of the security risks http://msdn.microsoft.com/en-us/library/ms188676.aspx

  • That only applies to scenarios that "resources that span multiple instances or servers". In this case we're talking about a single instance with multiple databases. – Jonathan Allen Apr 26 '14 at 7:51
  • 1
    My bad...I missed the part about it being within the same instance – stacylaray Apr 26 '14 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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