Solved

Best hard drive configuration for SQL Server

Posted on 2004-03-30
22
1,365 Views
Last Modified: 2009-09-01
This question may be more a hardware question than SQL Server but I thought I would post it here first.  I am about to order a new server from Dell that I will use only for SQL Server 2000 ( and SQL Server 2003 in the near future).   Bare with me, I am not up to date on hardware but I wanted to know what the best hard drive configuration would be for use with SQL Server.  The specs on the new server now contain 5 hard drives on a raid 5 array.  The first two drives are 36 gigs each and will hold the OS (Windows 2003 Server) and the other three are 146 gigs each that will be stripped and contain the database.  Regarding performance, is stripped the best option to use?

The database us used for a web based application written in ASP and ASP.Net that allows students to take tests online.  The test questions, answers, statistics for each kid are stored in the DB.  Also, the DB contains all enrollment information for each kid at participating schools.  The current size of the DB is 5 gigs.  At one time there may be from 50 to 100 users accessing this DB retrieving data for reports, retreiving data for tests or inserting new test information (test keys, student responses. etc) or class scheduling information.  I also use DTS to import large amounts of data into the DB from text files (30,000 to 60,000 records).

Aside from disk configuration are there any other areas dealing with hardware that I need to pay special attention to with reards to DB performance?

This server will also have dual processors at 3.2GHz with 1MB cache and 4gigs of DDR SDRAM.
0
Comment
Question by:km1039
  • 7
  • 6
  • 5
  • +2
22 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
The first two drives are 36 gigs each and will hold the OS (Windows 2003 Server) and the other three are 146 gigs each that will be stripped and contain the database.  Regarding performance, is stripped the best option to use?

To optain optimal performance, you really want small hard disk drives (smaller the better for a database application) and use RAID 10 (This requires 4 hdds, and you only get 50% of the available data -- also gives better higher availability than a RAID 5 solution).

Keep tempdb on a seperate HDD array if possible, and the T-logs on a different array to the main DBs.

All because you can buy big hard disk drives for a database, because the database size is going to be large -- doesn't mean it is a good idea.

With database systems, data access is non-sequential and determined by the number of hard-disk drive heads available for the process and 6 72GB HDDs will out-perform 3x146GB drives.

If you are on a limited budget R5 is better than using R10.

With this amount of memory/other items such as 4GB of RAM its a pity -- your DB app will be really HDD bound if purchasing these large drives...

For some basic hardware tips, looking here:
http://www.sql-server-performance.com/hardware_tuning.asp

Aside from disk configuration are there any other areas dealing with hardware that I need to pay special attention to with reards to DB performance?
Yes, lots -- but this depends on your application....
For example backups ?


The size/spec of a final server depends on a number of factors -- but the key ones are:
How long for backups/other offline maintance operations ?
How many T-sql transaction operations do you need to perform / second ?
How big do you expect the database to grow to ?

There is not enough information here, to say that this will be an OK configuration for your particular enviroment at the moment.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility

I agree with Dan about the need to ideally separate the
TEMPDB, & Transaction logs / backup Files onto there own drives...

I'd also recommend having a separate area for your DTS/Import/Export Requirements

again i'm unclear why when you say the database is 5GB, why your going for 146GB  drives...  

how are you dealing with "off site" backup will it be a tape drive?

you seem to be reserving 2 drives for the  Operating system
is that 1 for the OS and 1 for a paging file?


 
0
 

Author Comment

by:km1039
Comment Utility
Regarding backup operations.  The data is backed up (full backup) every night on a tape device that is located in the same noc room and Monday's tape is stored off site.

With regards to the hard drive it is better to have more disks with smaller capacity than a few disks with larger capacity because of the way SQL Server accesses the data randomly correct?  That makes sense.

"With this amount of memory/other items such as 4GB of RAM its a pity -- your DB app will be really HDD bound if purchasing these large drives..."
So given that the data is not loaded into memory then performance will take a hit when the DB engine has to search the larger hard drives.  

So given the current size of the DB at 5 gigs and assuming the database will grow to 50 gigs in the next 5 years (and I stay with 5 disks) I would be better off changing the size of disks from 146 to 72 at R5.  That means I would have 172 gigs for SQL server because you "lose" 20% with R5 right?

I would like to have one disk for OS and four for SQL but I will not have any falut tolerence if OS disk fails.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
So given the current size of the DB at 5 gigs and assuming the database will grow to 50 gigs in the next 5 years (and I stay with 5 disks) I would be better off changing the size of disks from 146 to 72 at R5.
--- Yes.

But you may well find that performance is not "fast", for a 50 GB DB System (Tempdb approx 20%-25% : 15 GB).
(So total space rqd: approx: 65 GB (excluding logs ?))

You will still be HDD bound, compared to the other components --
Transaction logs/database files / tempdb all have different access mechanisms and will be fighting for writes/reads on your R5 array.

That means I would have 172 gigs for SQL server because you "lose" 20% with R5 right?
Depends on the number of drives involved (4 -- yes about a 25% loss, 3: 33%) -- 1 drives total space is allocated for data-checking/redundancy across the array.  You could even fit the database on 3 R5, assigning 2 for R1 and the OS (better config here).

If this is the problem, then move the log files of the database to the OS partition (not great but will increase disk performance by keeping the logs away from the R5 drive system -- something to do for customers on a limited budget).
This gives your OS (based on 72 GB Drives) (RAID 1: Total Capacity 144GB, Usable Capactity: 72 GB  OS and Logs)
   (RAID 5 Array: Total Capacity 216 GB (3*72), : (2*72) Usable Capacity: 144 GB for DBs)

I do not recommend: 1 disk for the OS...I see problems ahead (sorry bad advert going round in my head)...

You are better off with the R1 and R5 configuration if you are limited by the number of drives to use/sizes etc..

0
 
LVL 3

Expert Comment

by:ctcampbell
Comment Utility
For SQL Server, more spindles are always better.  IMO, if you only have 5 disks, I would get the fastest ones you can afford (such as 15K drives) and make it a single RAID5 array.  I know Microsoft docs say to put the transaction logs on separate disks.  That's fine if you have enough disks for a mirrored set for the logs and a RAID5 for the data, but with only 5 disks, I think it's faster to put it all on a single array and use the additional spindles to their full advantage.  From your description, it doesn't seem like you will have a lot of concurrent write activity (I'm talking OLTP volumes here), and the reads really benefit from more disks.

I don't believe there is a need to specifically buy smaller disks, only expense.  15,000 RPM drives are very expensive and you might save some money getting smaller disks.  

There is no need to put the OS on it's own disk.  Once the server is up and running, the OS really doesn't do much of anything, disk-wise (unless it's swapping, which is a whole other problem).  I would, however, put the OS on a separate 4-8 GB partition.  Again, use all the spindles and redundancy you paid for.
0
 
LVL 13

Accepted Solution

by:
danblake earned 200 total points
Comment Utility
and SQL Server 2003 in the near future....

Its now SQL Server 2005 (Its running a bit late for public release)...

From @ctcampbell
 That's fine if you have enough disks for a mirrored set for the logs and a RAID5 for the data, but with only 5 disks, I think it's faster to put it all on a single array and use the additional spindles to their full advantage.

Wouldn't two seperate arrays be faster....
(see notes below)


A quick couple of notes from the web-site link I've provided:
Don't store your operating system, application software, or databases on single disk drives as single drives don't afford any fault tolerance

RAID 5:
Although this is the most popular type of RAID storage, it is also not the best option for optimum SQL Server I/O performance. If a database experiences more than 10% writes, and most OLTP databases do, write performance will suffer, hurting the overall I/O performance of SQL Server. RAID 5 is best used for read-only or mostly read-only databases. Testing at Microsoft has found that RAID 5 can be as much as 50% slower than using RAID 10

I estimate that the R5 performance is at least 20% slower than R1.


RAID 1
Ideally, the operating system and SQL Server executables, including the operating system's swap file, should be located on a RAID 1 array. Some people locate the swap file on its own RAID 1 array, but I doubt that this really offers much of a performance boost because paging, on a well-configured server, is not much of an issue.  

If your SQL Server database(s) are very small, and all the databases can fit on a single disk drive, consider RAID 1 for the storing of all your SQL Server data files.

Ideally, each separate transaction log should be located on its own RAID 1 array. This is because transactions logs are written to and read sequentially, and by isolating them to their own array, sequential disk I/O won't be mixed with slower random disk I/O, and performance is boosted.


0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
You might also want to bear in mind that Compaq (and a few other vendors) will not provide a fault-tolerance O/S on a Windows 2000 (2003) Server & SCSI Controllers unless in a R1 configuration ! (R5 is definitly not supported !)
0
 

Author Comment

by:km1039
Comment Utility
I did notice in the specs for this server that the three 146gig drives were 10K only and the 72 gig drives were 15K or 10K.  Im guessing the 15K or 10K is the rate at which the disk spins and there for the higher the RPM the faster the read/write time correct?  If that is the case then I should go with the 72 gig drives with 15K RPM.

Thanks for all the responses.  I am still trying to let all this soak in so to speak.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Yes, RPM is revolutions per minuite the higher the number the faster the heads go around the central spindles in the drive.  Faster the better.

If you can afford them I would buy 72GB 15K drives (if you are forced to only have 5 drives !) over the 146GB 10K Drives.

You may still find after you have purchased the system that you have plenty of hard disk drive capacity but if performance is a problem look at a SCSI Card with a battery backed up cache..Then if its still a problem you may want to look at something like a Compaq ML370 (Range) with an External StorageWorks enclosure allowing you to scale to 20 HDDs, with yet smaller drives sizes to increase the number of spindles/heads retrieving the data back to the system.  
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Five drives really is limited.  I would probably try something like this:

1 Drive (C): OS and tempdb (not ideal but you're limited)

2 Drives, RAID1 (D): Logs

2 Drives, RAID1 (E): Data
(no point in RAID5 with only two drives; in fact, no sure if it's even allowed)


It is *critical* that the logs and data *not* be on the same RAID array/disk.  Otherwise, if you lose that array (and it does happen), you will have no recovery except to restore the last backup.  [I guess if you can accept that much lost data, then you might get away with it, but it is still a very bad idea.]

Backups can go on (C) or (D) but not (E).  Otherwise, if you lose the drive, you will lose both the current db and the backups, that is, you'll have *no* way to get the db back!.
0
 
LVL 3

Expert Comment

by:ctcampbell
Comment Utility
In a perfect world, we could all afford a RAID 1/10 array for the logs and another RAID 10 array for the data files, for each database.  However fast these are, there is a 50% capacity penalty (and the associated price), whereas RAID 5 is only 1/n disks.  Each different configuration is a compromise between read performance, write performance, cost, fault tolerance, and capacity/storage efficiency (among other things, like how many drives you can physically fit in the server).
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
But total disk space really doesn't seem like an issue here, does it?  I'm initially most concerned about fault tolerance, specifically that data is not irretrievably lost!  Then about speed, then about space (more space can be added later if needed).
0
 

Author Comment

by:km1039
Comment Utility
I am not familiar with the difference in price from R5 to R10.  I do have to go with Dell as the manufacturer so I am going to chack their site again for other configurations.  I really appreciate all your help.  
0
 

Author Comment

by:km1039
Comment Utility
Since the disk size and configuration are in question here are my options:
I am going to get 4gigs of DDR memory and dual processors at 3.06 MHz with 1MB cache

Here are my options with regards to the hard drive and configuration"
up to 5 disks with 72gigs/15rpm or 146gigs/10rpm
configuration:
1 On-Board RAID 0, 1 to 5 drives connected to on-board RAID  
2 On-Board RAID 1, 2 drives connected to on-board RAID  
3 On-Board RAID 5, 3 to 5 drives connected to on-board RAID  
4 On-Board RAID 10, 4 drives connected to RAID  
5 On-Board RAID 0/ RAID 0, Split Backplane, 2-5 drives connected to RAID  
6 On-Board RAID 1/ RAID 1, Split Backplane, 4 drives connected to RAID  
7 On-Board RAID 1/ RAID 5, Split Backplane, 5 drives connected to RAID

What do you all suggest?
0
 
LVL 3

Expert Comment

by:ctcampbell
Comment Utility
It seems to me that given the limited number of disks, the easiest and perhaps most versatile configuration would be a single RAID 10 array with 4 disks (6 would be better, of course).  We're running our production databases on a server with a single 5-disk RAID 5 array, with no performance problems.  Knowing what I know now, I would have opted for RAID 10 instead of RAID 5.  I know the arguments for separating the logs.  However, with a online testing system, there is only so fast real users can write or answer questions (thus necessitating a DB write).  Even if you increase the load, reads are still going to outweigh writes.  Online transaction processing is a completely different story.

If it were me, I would go with the 4 x 72 GB 15K drives in a single RAID 10 (for 144GB total) or 5 x 72 GB 15K RAID 5 (for 288GB total), and partition it like:
C: - 8GB - Windows and SQL binaries
D: - CD-ROM :)
E: - the rest (136GB or 280GB)  - MS SQL data (including logs and tempdb)

You're free to disagree with me, but this type setup works well for us, performs well, is easy to set up and maintain, and we don't end up with the space allocated wrong (which is really hard to fix on a live production system).  I haven't used RAID 10 personally, but all the research points to it being the same speed for reads and much faster for writes than RAID 5.

P.S. 15,000 RPM drives are very loud and should be well isolated from anything with ears.
0
 

Author Comment

by:km1039
Comment Utility
I would like to go with the 6 disks config (cost is not an issue) but Dell does not offer that option on any models the I could see.

SQL binaries???  I'm not familiar with that.
0
 
LVL 3

Expert Comment

by:ctcampbell
Comment Utility
I appears Dell's 2650 (2U) and 6650 (4U) rackmount servers can only fit 5 drives max.  The tower servers, which can also be rack-mounted, will hold more.  The 2600 (5U) holds 6 drives (8 with optional media bay), the 4600 (6U) holds 8 (10 with optional media bay), and the 6600 (7U) holds 10 (12 with media bay).  Of course, if cost is really no issue and you can actually get all 12 drives to put in it, that completely changes what you can do with your disk configuration for maximum performance.
0
 

Author Comment

by:km1039
Comment Utility
I was looking at the 2600 and when configured with same processor and same memory as my provious config but with 6 drives at 73gigs/15rpm per drive the price jumped about $1000.  Is the extra disk worth the price increase?  How would you configure that?  Most of the processing and work load will be in retreiving data for reports.
0
 

Author Comment

by:km1039
Comment Utility
I also noticed that this server has an optional tape backup which would be added as well.  
0
 
LVL 3

Assisted Solution

by:ctcampbell
ctcampbell earned 300 total points
Comment Utility
Only you can decide if it's worth the price increase.  There is usually a noticeable improvement in read performance as you add more disks.  You could also go with 6 x 36GB drives (RAID 10, for 108 GB total), which should only be a little more than 4 x 72GB drives.  If you used RAID 5, you would get 180GB, at some cost to write performance.  It's really hard to say what's best for you.  You could even try calling Dell and asking, though I'm afraid whatever salesperson you get in Bangalore might not have a clue what you're talking about.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
7 On-Board RAID 1/ RAID 5, Split Backplane, 5 drives connected to RAID .

* As discussed earlier, in my last comment.

This way you can create a fault tolerant OS partition and DB Partition (with your 5 drives).
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you choose to use a single RAID5 array, I would be interested in hearing the results if you really reach 100 simultaneous users many of which are loading data.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now