optimum number of databases

[mjhessler]

Client written application using SQL Server was designed to be on local servers at each facility. Instead of individual local facility servers, each of the sixteen facilities has its own database on the corporate SQL Server cluster. All of the .mdf and .ldf files are on a SAN.

Should there be a performance difference between using the current sixteen databases verses a single corporate wide version? Either way SQL resides on the same hardware and supports the same user volume. The question is will SQL Server have an appreciable performance improvement in having a single application database verses having sixteen?

In general is breaking database applications into multiple database a way to improving server performance? Conversely would combining identical databases into a single database improve server performance?

 

[JayKusch]

If you have queries that span databases, ie facilities, you will see a marked improvement in response times if all the facilties were in the same database. Having them in one DB also eliminates the issue of cross-DB referential integrity

Thanks

J. Kusch

 

[mjhessler]

In this case queries DO NOT span databases, ie facilities. Each one is a stand alone and any data used by all facility databases exists in each. The amount of data stored would be less with the common database, but the traffic would not be distributed. It should be noted that these databases are all just copies with different database names and of course different facility data. Given the same amount of traffic does SQL Servers performance improve by simultaneously handling multiple databases instead of only one? Should databases in general be separated into multiple small databases on the same server to improve SQL Servers performance?