Storage and RAID recommendations for SQL Server 2000 or 2005 DB's.

Posted on 2008-10-27
Last Modified: 2012-06-27
I have a DBA consultant in my office checking our programmers code to find why our application is slow at times.  The DBA profssional found many things on the software side of things, but he also made strong recommendations on the hardware side.  I would like to hear your thougths on his recomendations.  First, a little about what we do:

My company is a custom software developer for a certain industry.  Our application is written in VB and our backend is SQL.  Over the years our application has gotten a little bit slow.  So we hired a DBA consultant to look at our infrastructure.  We currently recommend the minimum hardware:

DB Start Size = 1GB
Five Years later DB is about 5-8GB

* Windows 2003 Server Standard
* 2 Physical CPU's (Dual or Quad Core Technology)
* 4GB of Memory
* RAID 1 for OS (SAS) 10-15k RPM Drives
* RAID 5 for SQL DB (SAS) 10-15k RPM Drives

* Windows 2003 Server Standard
* 2 Physical CPU's (Dual or Quad Core Technology)
* 3GB of Memory
* RAID 1 for OS (SATA) 7200 RPM Drives
* RAID 1 for SQL DB (SATA) 7200 RPM Drives
and sometims a 3rd Volume with RAID1 for Trans Log (sometimes we have customers with 2 instances of SQL Server)

What do you guys think.  Below I will paste his over all drive layout requirement



SQL Server has different access patterns for the different types of databases. Temporary database has scatter / gather access pattern, while the transaction log is accessed in the sequential manner. The regular database is accessed in the various ways depending on the application type OLTP or DSS.
To improve the performance these databases and their files should reside on different volumes. All the volumes should be mirrored for redundancy and may be striped for performance. Hardware solutions (RAID controllers) are preferred over the software ones, as those can offload the work from the CPU.  These controllers can provide various levels of mirroring and striping. RAID 1 is the preferred level for mirroring, while RAID 10 (or 1+0) is for mirroring and striping. RAID controllers should have battery backed cache that allows to recover the data in cache in case of the power loss. RAID 5 should be avoided for performance reasons due to the implementation layout and checksum computation overhead. RAID 01 (0+1) should be avoided due to the recoverability issues, as it will make the degrade the whole mirror in case of any disk failure.
To configure the controller for the best performance it should be set to enable read-ahead cache algorithm, stripe size set to 128 KB, and individual disk cache enabled, which is also referred to as write-back as opposed to write-through. Also, disks should be connected to as many different channels on the controller as possible and the load should be spread evenly between the channels. Disk caching should also be enabled on the operating system side to increase the performance. NTFS partitions for the database should be formatted with 8 KB allocation block size, since it is the basic I/O size for the SQL Server. Note that 8 KB block size does not permit NTFS file compression that should be avoided in any case, as well as NTFS encryption.
Hard drives and respectively the controller should be SCSI, SATA, or SAS technology. IDE is not appropriate for the server performance requirements. Disk drives should be 7200 RPM or higher with 8 MB cache minimum. Ideally, the system would have a hot standby drive, but is not required.
The minimal performance layout is to have three independent volumes. The first one will have the operating system, pagefile, SQL Server binaries, system databases, database backup, and temporary database, which will be used most of all on this volume. The next one should have the database files only and the last one will contain the transaction log only.
To improve the performance further, the database files should be placed on a RAID 10 volume, while the rest can be on RAID 1, due to the disk access patterns. Temporary database and database backups should be on their own separate volumes.

Question by:BygRob
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

bjirgens earned 500 total points
ID: 22818887
If I read it right - you are saying you actually have a better drive configuration than what he is recommending? You state the DBA is recommending SATA drives but you have 15K SAS?

When it comes down to it - the easiest way to see if you are currently having performance problems with your drives is to run some performance monitoring. Use Windows Performance monitor and monitor average and current disk write queue and read queue length. This should not be more than 2 per drive in the raid set. If it is - then the performance problem you are having is storage related - not enough spindles / incorrect cache settings etc.

If it's not - then the problem lies elsewhere.

Author Comment

ID: 22818991
He is recommending SATA as a minimum.  Currently I recommend 15K SAS.  He also is taking the RAM down to 3GB - he says will handle better allocation.  I will log on to a customer that has SATA and run the performance counters you noted above.  And I will compare to a customer with SAS.  Will post in tomorrow AM.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

740 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