Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
We look at whether swapping a controller board on a failed hard drive is likely to solve the problem.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

704 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