• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

SQL Server - Drive speed question

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?
3 Solutions
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.
Scott PletcherSenior DBACommented:
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.
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.
KaffiendAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now