Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Hard Drive Configurations for SQL Server

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (19)   |   Related Tips: More > Hardware

Problem
At my company we are looking at purchasing a new SQL Server and I am not sure how many disk drives we should purchase.  We do not have an unlimited budget because we are a small company, but I am interested in how we should configured disk drives for SQL Server if we bought 1, 2, 4, etc disk drives.  I also am interested in the rationale behind the decisions.  Can you shed any light on the subject?

Solution
Let's address the second part of your question question first.  The rationale behind spending time building an appropriate hard drive configuration is to ensure the disk drives and the remainder of the resources (i.e. CPU, memory, etc) will meet the application needs for a finite period of time such as 2 or 3 years. You do not want to under estimate the needs and have disk IO problems or have the need to replace hardware every 6 months as an example.  In addition, it is valuable to ensure your disk drives will have proper redundancy to provide basic fault tolerance to ensure the SQL Server component of the architecture will meet the business needs.

SQL Server Components

From a relational engine perspective, the following items can be considered as having different types of processing on a SQL Server machine or can be considered a different component requiring special needs from a disk drive perspective:

  • Windows
  • System Databases
    • Master, Model, MSDB
    • TempDB
  • User Defined Databases
    • Database File groups
      • Primary
      • Indexes
      • Data - Read\Write
      • Data - Read-only
      • Data - Specific tables
  • Database Transaction Logs
  • Backups
  • Full Text Catalogs
  • Batch Processing

Capacity Planning

Before you select a disk drive configuration, it is wise to determine your general needs rather than purchasing too few disks and having to upgrade in the near term or purchasing too many disks that are not needed in the long term.  To get a feel for your disk drive needs the PhysicalDisk counters in Performance Monitor should shed some light on the situation.  Specifically, the transactions per second, read, write and transfer operations as well as disk queuing counters should give you a good sense of disk drive metrics.  Then hopefully you can translate these metrics into disk drive needs with the hardware vendor.

Table Legend

Below outlines the table legend for the next few sections:

  • Disk - Disk number from a physical disk perspective
  • Quantity - Number of disk drives
  • RAID - Redundant array of inexpensive disks configuration
  • Volume - Windows drive letter
  • Purpose - The functional SQL Server component that the disk drives are supporting

Configuration = 1 Disk Drive

Although this is not the idea configuration for redundancy, I am sure many development and test SQL Servers have a single disk drive at some organizations.  It is just the reality in some circumstances, but is without any disk redundancy be sure to understand the potential for a failure.

Configuration = 1 Disk Drive

Disk Quantity RAID Volume Purpose
0 1 0 C:\ All functions

Configuration = 2 Disk Drives

With 2 disk drives, a single RAID 1 set can be leveraged to ensure any 1 disk drive does not yield a failed server.  This is the most basic redundant disk configuration.

Configuration = 2 Disk Drive

Disk Quantity RAID Volume Purpose
0 2 0 C:\ All functions

Configuration = 4 Disk Drives

With 4 disks opens up the option to have either one RAID 5 set or two RAID 1 sets.  With the one RAID 5 set you have the opportunity to support additional storage needs as compared to the two RAID 1 sets.  With the one RAID 5 set only one quarter of the raw disk space is lost to support the parity as compared to the two RAID 1 sets where half of the raw disk space is used for parity, so only half of the disk space is usable.  On a similar note, a RAID 10 set can be used which also yields half of the raw disk space as usable.  Here are some possibilities to support those needs.

Configuration = 4 Disk Drive (One RAID 5 Set)

Disk Quantity RAID Volume Purpose
0 4 5 C:\ All functions

 

Configuration = 4 Disk Drive (Two RAID 1 Sets)

Disk Quantity RAID Volume Purpose
0 2 1 C:\ Windows, System Databases, Backups, Batch Processing, Full Text Catalogs
1 2 1 D:\ User Defined Databases and Transaction Logs

 

Configuration = 4 Disk Drive (One RAID 10 Set)

Disk Quantity RAID Volume Purpose
0 4 10 C:\ All functions

Configuration = 8 Disk Drives

Once you have a server with 8 or more disks, how the disk drives are configured is really dependent on the needs.  For example, if your SQL Server has a small user defined database that needs to be split up as much as possible to balance the IO needs this is a very different configuration then having a large user defined database that is not IO bound or having to split the IO load between user defined databases and tempdb.   Depending on the disk storage needs and the performance needed, the RAID configurations could be RAID 5 or 10 to support some of the SQL Server functions.  Although, an even number of disk drives are needed for the RAID 10 set.  In addition, typically 4 or more disks are used with the RAID 10 set and 2 disks are used for a RAID 1 set, but some terminology is different based on the hardware vendor's disk configuration interface.  As you can see with more disks you have more options, so below are a few different options that benefit different scenarios. 

Configuration = 8 Disk Drive (Balanced Approach)

Disk Quantity RAID Volume Purpose
0 2 1 C:\ Windows, Backups, Batch Processing, Full Text Catalogs
1 2 1 D:\ System Databases (TempDB)
2 2 1 E:\ User Defined Databases
3 2 1 F:\ Transaction Logs

 

Configuration = 8 Disk Drive (Large User Defined Database)

Disk Quantity RAID Volume Purpose
0 2 1 C:\ Windows, Backups, Batch Processing, Full Text Catalogs
1 2 1 D:\ System Databases (TempDB), Transaction Logs
2 4 5 E:\ Large User Defined Database

 

Configuration = 12 Disk Drives

This configuration just expands on the last option in order to support each of the functional areas in SQL Server.  Once again, based on your exact needs dictates exactly how the disk drives should be configured, but below are examples of a balanced approach and a multiple file group approach.  In addition, with this approach the backups are written to a RAID 0 set which does not have any redundancy so a maximum amount of storage is available for the backups on the local machine for the fastest possible local recovery.  As indicated above, depending on the disk storage needs and the performance needed, the RAID configurations could be RAID 5 or 10 to support some of the SQL Server functions.  Although, an even number of disk drives are needed for the RAID 10 set.  In addition, typically 4 or more disks are used with the RAID 10 set and 2 disks are used for a RAID 1 set, but some terminology is different based on the hardware vendor's disk configuration interface.

Configuration = 12 Disk Drive (Balanced Approach)

Disk Quantity RAID Volume Purpose
0 2 1 C:\ Windows
1 2 1 D:\ System Databases (TempDB)
2 4 5 E:\ User Defined Databases
3 2 1 G:\ Transaction Logs
4 2 0 H:\ Backups, Batch Processing, Full Text Catalogs

 

Configuration = 12 Disk Drive (Multiple File Group)

Disk Quantity RAID Volume Purpose
0 2 1 C:\ Windows, System Databases (TempDB), Transaction Logs,  Full Text Catalogs
1 2 1 D:\ User Defined Databases (Indexes)
2 4 5 E:\ User Defined Databases (Data)
3 2 1 G:\ User Defined Databases (Specific Tables)
4 2 0 H:\ Backups, Batch Processing

Storage Alternatives

As you select the brand of server you want to use in your environment, also consider the storage options that available from the vendor.  Some vendors have external arrays, have their own NAS\SAN products or have partnerships with NAS or SAN vendors.  If your hard drive needs exceed the options listed above then consider these options.  In addition, if you are looking to consolidate SQL Servers or migrate to blade servers, be sure to fully understand the options available with the storage so that the storage sub system can meet the current needs and scale to meet future needs in a cost effective manner.

Next Steps

  • As you consider the hard drive configuration for your next SQL Server, consider this tip as a point of reference to configure the disk drives.
  • Although many of these disk drive configurations provide a great deal of redundancy, do not fool yourself by not taking backups.  Be sure to have a thoroughly tested backup and recovery strategy to ensure a 1 or 2 disk failure will not yield a down application and major fire fighting by you.
  • If you standardized on a hardware platform and disk drives, consider purchasing additional disk drives to keep on site for quick replacement in addition to 2, 4 or 8 hour support agreements from the hardware vendor.
  • Rebuilding a disk drive configuration once the server is in production is not a pleasant process, so be sure to understand the needs and the options available with the disk drives so you are not faced with extensive downtime to rebuild the disk drives and potentially re-install all of the software.


Last Update: 9/12/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, April 24, 2012 - 4:43:09 AM - Orion Pax Read The Tip

Hi Jeremy, if you require additional space and performance on a 8 drive configuration why whould you not go for somethibng like: 

Configuration = 8 Disk Drive (Largish User Defined Database)
Disk Quantity RAID Volume Purpose
0 2 1 C:\ Windows, Backups, Batch Processing, Full Text Catalogs, Storage
1 2 10 D:\ User Defined Databases
2 4 1 E:\
System Databases (TempDB), Transaction Logs

Tuesday, April 24, 2012 - 9:23:02 AM - Jeremy Kadlec Read The Tip

Orion,

In that configuration, Disk 1 has a quantity of 2 drives and Disk 2 has a quantity of 4 drives.  I would assume you would want to swap the purpose of the disks because your user defined databases would be larger than TempDB and the transaction logs.  Beyond that, if you have only 8 drives in your server that could be a viable option.

Hope that helps.

Thank you,
Jeremy Kadlec


Tuesday, April 24, 2012 - 10:04:07 AM - Orion Pax Read The Tip

Hi Jeremy, thanks for the feedback.

Yeah, disk 1 should have a quantity of 4 and disk 2 should have a quantity of 2. RAID 1 only supports 2 and RAID 10 a minimum of 4.


Wednesday, June 27, 2012 - 8:54:51 PM - Jon Read The Tip

Jeremy,

Let's say I have the 12 disks as you state above. Which option would work better for multiple instances (the balanced approch or multiple file group)? We will be running SCCM, SCOM, and WSUS.

 

Thanks

Jon

 


Tuesday, July 31, 2012 - 10:25:36 AM - Sanyog Singh Read The Tip

Which RAID level is best suited for following considering a)Cost and b)Performance -
1. Database files
2. Log files
3. Tempdb


Tuesday, July 31, 2012 - 12:48:03 PM - Jeremy Kadlec Read The Tip

Sanyog,

Let me see if I can help give you some general pointers.  Based on your question, I am not sure I can give you an absolute answer.

In terms of cost, traditional spindles are the cheapest and solid state disks are the most expensive in terms of a single disk.  You also have SAN's with both spindles and solid state technology.  The SAN's with spindles can use SAS, SATA, etc. technology.  These vary in price, redundancy, etc. based on the vendor. 

In terms of RAID sets, with direct attached storage (DASD), in general RAID 0 is the fastest, but you have no redundancy.  Generally, RAID 10\RAID 1+0\RAID 0+ 1 is the fastest with redundancy, but the price is high.  RAID 5 is generally a middle ground many systems use in order to balance performance and cost.

As this tip outlines, I would make sure you understand your needs as compared to the disks available then select the best disk configuration you can afford with the resources available.

HTH.

Thank you,
Jeremy Kadlec


Monday, December 10, 2012 - 6:49:19 PM - Tim Jaco Read The Tip

Is a dedicated RAID 1 set for Windows necesseary? Windows Server 2008 (and up) when given plenty of RAM, pretty much just runs in memory with minimal swap file I/O. Would it be a big performance sacrifice to co-locate SQL logs with the OS? 


Tuesday, December 11, 2012 - 9:42:21 AM - Jeremy Kadlec Read The Tip

Tim,

Thank you for the post.  The general recommendation is related to ensuring a single disk drive failure does not yield downtime.  Depending on the drives you have available dictates the best solution in your circumstances.

Thank you,
Jeremy Kadlec


Monday, December 24, 2012 - 11:15:19 AM - Dhamendran Read The Tip

how to set an alert when disk drive reaches 70%.


Monday, December 31, 2012 - 9:32:30 AM - Jeremy Kadlec Read The Tip

DHamendran,

Have you tried this?

http://support.microsoft.com/kb/324796

HTH.

Thank you,
Jeremy Kadlec


Tuesday, February 26, 2013 - 9:08:16 AM - tavher Read The Tip

Hi!

Please help me with configurations with low cost servers which are already set at our customers. Which configuration would be optimal with only 2 HDDs and only one user database with 500MB to 2GB max.

Variants of HDDs are:

1: 2x SATA or SAS (no RAID)

2: 1x SATA or SAS (RAID 1 - mirror) and 1x SATA or SAS

3: 1x SATA or SAS (no RAID) and 1x SSD

4: 1x SATA or SAS (RAID 1 - mirror) and 1x SSD

Which files should I put on which HDD in these variants and on which HDD should be the operating system with SQL server installation?

 

Thank you!


Monday, March 04, 2013 - 9:08:36 PM - Jeremy Kadlec Read The Tip

Tavher,

Based on the information provided, I would always use a RAID set(s) in production to prevent a single disk from causing a failure.

With that being said it looks like option 2 or 4 would be it.  I am not sure about the additional SATA or SAS or SSD disks that you mention in those options since you said there are only 2 disks possible in your first paragraph.

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, April 05, 2013 - 3:13:30 PM - Sapen Read The Tip

Hi Jeremy,

 

We have replaced SAN Drives with SSD Drives and put TX Log files (L:\ drive) with Raid 1+0 and Tempdb (T:\ drive)with Raid 1+0. However we still left the OS and system databases(C:\ drive), Backups(G:\drive), Error logs (I:\ drive) , data files of user databases (M:\ drive) on the exisiting SAN drives with Raid 5. I first performed a speed test on SSD's before replacing and noticed 13x speed differences. However we havent noticed any difference even after replacing the SAN drives with SSD's. The wait times still remain the same. Most of the waits are caused by insert statements that are inserting into tables with clustered indexes.

We are using SQL Server 2008 R2 EE, SQL Server 2008 R2 SE, Memory: 56GB.

Max SQL Server mem setting: 46GB

Can you please advise how can we improve performance? 

 

Thanks a ton in advance


Friday, April 05, 2013 - 3:23:30 PM - Sapen Read The Tip

The inserts are causing the PAGEIOLATCH_EX wait type


Friday, April 05, 2013 - 5:28:14 PM - Jeremy Kadlec Read The Tip

Sapen,

Thank you for the post.  Let me see if I can ask a few more questions to try to offer some advice.

  • What test did you run that yielded a 13x speed difference?
  • Once you implemented the SSD can you duplicate the 13x speed difference?
  • Have you considered moving the databases or file groups to the SSD drives?
  • Are you looking at your wait stats in the aggregate and not seeing any differences? Or are you capturing deltas then making a comparison with historical and current data?
  • Have you reviewed all of the indexes on your tables to ensure they are all necessary?
  • Have you considered splitting your non-clustered indexes from your data\clustered indexes onto a separate file group on a separate disk?

Let me know the answers to these questions and I will see if I can provide some further suggestions.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, April 05, 2013 - 10:44:44 PM - Sapen Read The Tip


•What test did you run that yielded a 13x speed difference?
•I ran the SQLIO stress test

•Once you implemented the SSD can you duplicate the 13x speed difference?
•This test was performed on a test server. Did not run the SQLIO stress on our production env.

•Have you considered moving the databases or file groups to the SSD drives?
•Yes. Currently we have moved the user databases transaction log files to an SSD drive with RAID 1+0 (to drive L:) and TempDB to SSD drive with RAID 1+0 (to Drive T:\)

•Are you looking at your wait stats in the aggregate and not seeing any differences? Or are you capturing deltas then making a comparison with historical and current data?
No I am using confio to keep track of wait stats. Confio shows us wait time for each individual sql server query. Before replacing the SSD's the wait times were the same as they appear now.

•Have you reviewed all of the indexes on your tables to ensure they are all necessary?
•Yes there are 2 nonclustered indexes with included columns  and a clustered index.

•Have you considered splitting your non-clustered indexes from your data\clustered indexes onto a separate file group on a separate disk?
No.

 


Monday, April 08, 2013 - 10:51:47 AM - Jeremy Kadlec Read The Tip

Sapen,

Please see my comments inline.

•What test did you run that yielded a 13x speed difference?
•I ran the SQLIO stress test

> OK - I am not sure that is always representative test based on individual SQL Server IO workloads.

 

•Once you implemented the SSD can you duplicate the 13x speed difference?
•This test was performed on a test server. Did not run the SQLIO stress on our production env.

> OK.

 

•Have you considered moving the databases or file groups to the SSD drives?
•Yes. Currently we have moved the user databases transaction log files to an SSD drive with RAID 1+0 (to drive L:) and TempDB to SSD drive with RAID 1+0 (to Drive T:\)

> Are you having issues with your database transaction logs and TempDB?  Based on the information provided, the issues seem to be the database files.  Based on the information provided, I think those need to move to the SSD drives.

 

•Are you looking at your wait stats in the aggregate and not seeing any differences? Or are you capturing deltas then making a comparison with historical and current data?
No I am using confio to keep track of wait stats. Confio shows us wait time for each individual sql server query. Before replacing the SSD's the wait times were the same as they appear now.

> OK - So are the wait stats showing issues with your transaction logs and TempDB or your database files?  If it is the database files, then you need to move those to the SSDs.

 

•Have you reviewed all of the indexes on your tables to ensure they are all necessary?
•Yes there are 2 nonclustered indexes with included columns and a clustered index.

> OK - I suspect the issue is with the database file IO load, so based on the information provided I would try to move the database to the SSD and see what the performance gain is before making index changes, unless you know indexes are missing and queries are scanning.

 

•Have you considered splitting your non-clustered indexes from your data\clustered indexes onto a separate file group on a separate disk?
No.

> OK - Based on the information provided, I would move your database to the SSDs prior to any other changes.

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

 


Monday, April 08, 2013 - 4:51:30 PM - Sapen Read The Tip

Jeremy. Thanks Much for the reply. since the write operations occur in database Log files I put them in SSD's with RAID 1+0. So you think Raid 5 SSD's for database data files should be fine?

Thanks Again.


Monday, April 08, 2013 - 5:14:34 PM - Jeremy Kadlec Read The Tip

Sapen,

Without being able to see the workload myself, I would test the configuration and verify the results. 

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.