?
Solved

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

Posted on 2008-10-27
2
Medium Priority
?
750 Views
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

CURRENT SQL SERVER REQUIREMENTS
* 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

DBA CONSULTANT SQL SERVER REQUIREMENTS
* 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 CONSULTANT WRITE UP ON DRIVE LAYOUT

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.


0
Comment
Question by:BygRob
[X]
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
2 Comments
 
LVL 1

Accepted Solution

by:
bjirgens earned 2000 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.
0
 

Author Comment

by:BygRob
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.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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.
This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

800 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