• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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

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.

1 Solution
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.
BygRobAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now