Are cross-database queries expensive in SQL Server? All of the databases are in the same instance.
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
...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.
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