Splitting SQL DB across HDDs

Arturs

So has anybody tried to split SQL database across many HDDs to increase overall database performance ? Is it cost-effective ?

themave

here was a good description on SQL setup

http://www.mibuso.com/forum/viewtopic.php?t=8059

bbrown

It depends on what you mean by "split SQL database across many HDDs"

If you mean:

1. Place the database on a RAID 10 array supporting a large number of physical drives:

Yes.

2. Spreading the database objects across several physical files. Each placed on its own drive.

No.

Arturs

bbrown wrote:

1. Place the database on a RAID 10 array supporting a large number of physical drives

It's what I mean 8)

Is it cost-effective and can avoid tablelocking of 70 concurent users ?

Thnx

bbrown

Locking is caused by your user's interaction with your database. Your hardware will not stop or create locking.

Improving system performance will reduce the time required to execute a specific event. Even though the same locks occur, the users will see better performance and may view it as a reduction in locks. A poorly performing server can easily be viewed as a locking problem by users.

That being said, moving to RAID 10 can help system performance. But don't neglect the rest of your system.

How big is your database?

Describe your current system.

kine

And you need to think about this:

8 HDDs connected in RAID1 in config 4x2 HDD (4 pairs) with 4 DB files will be better than 8 HDDs in one RAID10... Why? Because if you split DB into more files on separate HDDs the access speed is lower and SQL can optimalize the access to the files...

bbrown

SQL will not optimize its access across multiple database files. If you split the database, you must decide which database object will reside in which files, and configure them manually. The one restriction is that a table and its primary key must be in the same file.

Placing the database files on RAID 1 also also introduces the maintenance task of reconfiguring the database whenever a file outgrows its drive set.