SQL Server Performance Forum – Threads Archive

Perf Overhead of multiple databases?

As with most systems, my OLTP system has a bunch of reference data tables, which don’t change very often (hundreds of times a day) such as CUSTOMERS, and several transaction tables, which are very active at adding new rows (tens of thousands a day) such as PAYMENTS. The current DBA has these broken out into separate databases so that he can have a different backup/recovery model for the reference data than for the high-volume data. However, many of our queries and reports need to join across CUSTOMERS and PAYMENTS. The current DBA believes the performance slow-down of joining tables across databases is trivial, but in my Oracle experience, this is totally the wrong thing to do because the overhead is in fact quite big. Any opinions or knowledge on this would be great. Thanks.

It’s not that big unless they’re on seperate servers. There are some security concerns (read up on cross-database permissions), but the performance hit if they’re on the same server is negligible. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

What are the sizes of databases and their growth?
http://www.sql-server-performance.com/federated_databases.asp for reference. General online performance shouldn’t be impacted either by using one large database or a series of smaller databases. SQL Server doesn’t cache entire databases, it caches data pages, and it doesn’t matter if all of the pages in memory are in one database or if they’re spread out across 15 databases. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>