Solved

SQL Server - Drive speed question

Posted on 2014-04-01
4
403 Views
Last Modified: 2014-04-08
Hypothetical question:

Planning a MS SQL server.  I would like some inputs/insight regarding storage performance.

I believe that for optimum performance, each of the following should be on separate drives: OS, database, index, logs, tempdb.

If the above statement is correct, and I could only have 1 SSD drive (and the others on spinning drives), which of the 5 should I put on the SSD drive for the best performance?
0
Comment
Question by:Kaffiend
4 Comments
 
LVL 6

Accepted Solution

by:
deiaccord earned 250 total points
ID: 39971739
The reason for having the functions on seperate drives is because hard drives are quite slow at random read operations as it will take a few milliseconds to move the read heads to the correct position to read /write the data.
For a fast hard drive the number of read/write operations they can manage in a second (IOPS) might be measured in the hundreds. For a SSD drive this might be a hundred thousand!

The best advice will vary based on your specific workload required of your database but a SSD may be able to handle the workload of all of the functions better than several hard drives in an arrays subject to space considerations and data redundancy.

(If you only have 1 SSD then data redundancy may be a bigger potential problem than performance should that single drive fail!)

With the above said the OS would be the first function I wolud not put on the SSD (once loaded it will not affect SQL performance so a moot point).
SQL Logs are sequential writes so would not benefit from a SSD as much so would be another quick canditate for being on spining disks.

Database,indexes and tempdb will vary with your workload as to which will give the biggest performance benefit. I would be tempted to put tempb and the indexes on the SSD first as these are going to have the biggest benefit from quick random read/writes. You could put the database on as well as it will still likely be faster than having it on a seperate drive array if you are not limited by space.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
ID: 39972584
Tempdb would be first.  Then critical small clustered and non-clustered indexes.

The OS doesn't really need to be on a separate drive set.
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 200 total points
ID: 39975876
You don't really gain performance by having separate disk sets for each function, if you buy the same number of disks as you were going to buy but put them all in one big RAID 10 then performance should be similar to what it would have been on separate sets of spindles.

If the database, index and tempdb are on the same spindles that slows it down but the total number of spindles available to tempdb (for example) is three times what it would have been before and therefore it has *more* IOPS available. Consider if it was on a SAN, chances are you wouldn't even know whether the LUNs were on the same disks or not. The main advantage of lumping all the storage together is that if you overestimate the IOPS needed for one task the spare IOPS are available for one of the other jobs.

Note this doesn't include the transaction logs, they should be on separated disks for recovery purposes (assuming full recovery model) and also because their I/O profile is so different from anything else. Logs are sequential, you can get away with a pair of 7.2K disks normally, they have slow random access but excellent sequential access due to the high areal density.
0
 
LVL 14

Author Closing Comment

by:Kaffiend
ID: 39987483
Thank you everyone for the suggestions and comments.

I'm going to put them to good use.

I especially appreciated that an SSD (in at least a RAID1 configuration) could more than adequately handle several tasks that might at one time have called for separate drives.

Point also taken about the increased IOPS available from a large RAID10 vs several smaller disk sets.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

757 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

22 Experts available now in Live!

Get 1:1 Help Now