From: Ellen K. 

The one that's in full production has somewhere between 400 and 500 users
and now that they're up to speed with the application they're adding about
1GB a week.  Right now it's only about 32 GB but at this rate it will be a
lot bigger in no time.  This is the one with the recordings.

In general for a database if you can configure the box to have the
transaction log on its own drive(s) you're already way ahead, because the
log is constantly being written to.

The reason it's good to have the nonclustered indexes on their own drive(s)
is that the nonclustered index records point to the clustered index, not to
the actual data, so it's always a two-step.  If the nonclustered index is
on a different drive from the clustered one, it doesn't have to lose its
place every time going back and forth.

Disk I/O is the most important factor in database performance, and a lot of
memory is a good idea too.

However (or of course) the above won't completely make up for a stupid
database design.  The database design is extremely critical for
performance.   SQL Server defaults to making the primary key the
clustered index (although you can override this), and you can only have one
clustered index per table.   The data are physically stored in the
order of the clustered index.  Therefore you should use natural primary
keys whenever possible.  For example, suppose you have a contract status
history table -- your PK should be the contract number and a sequence
number, that way all the records for each contract stay together.   If
you take the newbie way out and use identity columns (an otherwise
meaningless automatically incremented number that uniquifies each record)
for your PKs you not only open the door to duplicate data (rows which are
the same except for the artificial PK), you're storing the rows in the
order in which they're written to the database... To go back to our example
of   contract status history, the application is going to
want to see the history of a particular contract, which obviously it can
fetch much faster when finding the first row means it's found all of them. 
 As Joe Celko has pointed out, the artificial PK is really a
reincarnation of the "record number" used in the Bad Old Days of
flat files.

On Mon, 22 Mar 2004 17:20:22 +0200, "Antti Kurenniemi"
 wrote in message :

>Can you tell me a bit about what sort of load you have on those servers?
>Like, how many users and roughly how much data? I'm really interested,
>because it looks like we may be able to finally (well maybe perhaps could
>be...) get rid of the built in database server that comes with our
>operational system, and use SQL server for it.
>
>
>Antti Kurenniemi
>
>"Ellen K." 
wrote in message
>news:7jvt509inlbhuhct111p29nrm2ur6p2ta5{at}4ax.com...
>> This is interesting.   The configuration I came up with for my SQL
>> Server boxes is:
>>
>> 3 drives in a RAID5 array - for the data and the clustered indexes
>> 2 drives in RAID1 (mirrored) - for the transaction log
>> 1 solo drive for the nonclustered indexes
>>
>> This optimizes performance of the database.
>>
>> I wish I didn't have to have the nonredundant drive, but our servers all
>> only have six drives.  I have a script they can run if the solo drive
>> dies that DROPs all the nonclustered indexes and then rebuilds them on
>> the RAID5 "drive" until they can replace it.  The
database would be slow
>> during this period but could stay up.  But presumably they would get a
>> SMART warning in time to back it up so they wouldn't even have to use
>> the script... but either way they would have to take down the box long
>> enough to swap out the drive.
>>
>

--- BBBS/NT v4.01 Flag-5