Go Premium for a chance to win a PS4. Enter to Win


SQL Server - Drive speed question

Posted on 2014-04-01
Medium Priority
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?
Question by:Kaffiend

Accepted Solution

deiaccord earned 1000 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.
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 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.
LVL 56

Assisted Solution

andyalder earned 800 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.
LVL 14

Author Closing Comment

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.

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

926 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