How many databases can I have in a sql 2000 standard server before
adversely effecting performance.  I know the limit is 32,000
databases, but what are the performance implications of creating that
many databases.

We have a situation where we need to create a database for each
customer, and each database will contain a few tables, a few stored
procs, and an average of 100,000 records.  We have about 400 new
customers a year.  The server is a very fast dual processor server,
with 2 gigs of ram.

Please let me know if you need additional information.

---

Request for Question Clarification by jbaltzell-ga on 04 Jun 2004 11:23 PDT

I think it is less of a matter of how many tables you have with how
much data, but how many transactions are made on average by each
database on the server.

Do you have any kind of metrics on usage that you could provide us? 
Are all the Stored Procedures the same?  Are there a lot of joins and
complications that would tax the processor?

The amount of memory seems like it should be fine, the processors
should last a few years, the only concern I would have for growing
slowness would be the drives.  Are they SCSI?  I would think that
having a lot of transactions from a lot of different databases would
be hard on the storage since it would have to constantly seek the
drive to find the correct data.

---

Clarification of Question by jthoma1-ga on 04 Jun 2004 11:40 PDT

I don't have an specific metrics, but each database will have very
similar stored procedures.  Most of the stored procedures are fairly
simple queries with just 1 or 2 joins.  Some simple Group By X,Y,Z and
select Product Name, Count(*).  A few stored procs are very intesive
queries, which create temp tables, join temp tables, and although most
databases won't have more than 100,000 records, a few will have over
20,000,000 records.  Each database will only have a few users, and
these stored procedures will only be executed a few times a day.

Our main concern is creating so many different physical databases on 1
server.  Other than management headaches to keep the databases up to
date, are there any inherent problems with having 1 sql server that
contains upwards of 100, 1000, 10000 databases?

We do have a RAID 5 SCSI drives.  And we will soon be switching to a SAN.