Multi Database performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Multi Database performance

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-10-18 : 03:59:04

I think this has been asked before but can't find/can't remember.

Which is better?
Having a number, eg 10, of small databases with a small number of tables < 15 with relatively small quantities of data, generally less than 1000 rows which gets hit a relatively small number of times, maybe 1000 hits a day, each.
or
Put all these tables into one database, will still all be seperate tables but within the same database.

In option 1 each database is the backend to a small web application and get hit through a mixture of ADO and ADO.Net.
I am looking for an overall preformance only answer, the developers will argue the administrative point of having a seperate database for each app etc.
My gut feeling is that the more datbases there are to manage the more resource hungry SQL server will be.
Am I right/wrong, why?

JamesH
Posting Yak Master

149 Posts

Posted - 2002-10-18 : 08:49:10

I don't see any performance improvements by what you suggest. It would be my 'preference' if all of these databases were identical to Normalize it and have one database with a few tables that are very tall versus many tables that have the same structure and are exclusive to say, a customer.

If these databases are not the same structure and their use is not related then I would suggest splitting them into different databases and managing them independently. You have to consider what the product will be like 3-5 years down the road.

I think the only difference might be some maintenance issues and memory allocation. SQL will allocate more memory, 3924 bytes plus 1640 bytes per file and 336 bytes per filegroup, for each database (2000).

JamesH.