SQL Server - Drive speed question

Posted on 2014-04-01
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 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.
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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.
LVL 55

Assisted Solution

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.
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 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