SQL Server disk configuration

By jhenderson ·
One of our SQL servers has an interesting disk configuration. I am wondering if the community can provide me some feedback.
The server has the data broken down and placed among many different logical drives, 19 to be exact. Each drive letter is made up of a pair of 15k scsi drives mirrored using HP RAID utility. Our main database on this server has the filegroups and ndf files all on thier own hard drives, which are mirrored. The performance of the server is good. We never recieve complaints about it. The DBA who created this server left the company so I cannot ask him about it.
Can I please have any and all comments regarding this? I can add more details as necessary.

Thank You all in advance.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Splitting Data in MS-SQL

by gsquared In reply to SQL Server disk configura ...

Splitting data across multiple RAID sets in MS-SQL is a good idea that will, if done right, result in faster reads and writes. This is especially true if you have tables with a lot of heavy use and you put the different tables on different drives.

It means you spend less time spinning the disks to get from table to table. (Seek-time)

The log files should also be on a separate drive from the database files, both for safety reasons (helps in case of drive crash) and for performance reasons (less drive seek-time).

So, yeah, this is a pretty standard thing to do and is a "best practice".

Oh, and tempdb should be on a separate drive array from the rest of your databases, since that one gets accessed every time any database on the server needs to do an "Order By" that won't fit in RAM, and various other temp table kinds of things.

Collapse -

Two sides to this

by Tony Hopkinson In reply to SQL Server disk configura ...

the mirroring is for hardware failure.
Breaking the database up across different disk drives is performance.

Well actually it's a performance enhancement if you do it right.
SQL Server will let you split a database across physical files. The files of course can be on different drives.

By itself this is not a lot of use, because database entities (tables etc) are stored in groups.

If you have the one group, what actually happens is it will use each file successively. So if table one has 20 records across nineteen files, The first file will have records 1 and 20 in it the others 2 to nineteen.
What you can do though is define a group (or more than one) for each file and then define tables within the group.
So for instance you could put your most dynamic tables on the fastest disk. Or you can relate them logically, so you could put sales tables in one group and purchases in another as rarely will any process need to access both at the same time.
The other way is to say most of the time I need these three tables at the same time, so I'll put each one on a separate drive so they can be read simultaneously or at least an approximate thereof.

Like any optimisation, it all depends on the environment, there's no hard and fast rule of how to do it, just that at a certain point it's probably not worth the effort of doing any more.

The facility is actually in there for scaling enterprise solutions, but there are a lot of nice things you can do with it such as read only file groups and rotating in tables split by calendar periods.


Related Discussions

Related Forums