From: "Antti Kurenniemi" 

Thanks, Ellen. The database design is not an issue to us at this point
because we can't do anything to it - and it is disaterously bad. Duplicated
data all over the place, sucky indexing (useless ones + missing some
important ones), and we can't change it because it's the "built
in" version. I'm planning on breaking the database on to different
disks, because at the moment it's all in one big file but most of the stuff
goes on in only four tables, so I think if I could break those, or at least
their indexes, it might make a big difference.

I'm really looking forward to getting to play with SQL server :-)


Antti Kurenniemi

"Ellen K."  wrote
in message news:j1gv505oi89pe5oj6vlue9b1uf2gf64i8m{at}4ax.com...
> 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.

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