Solved

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

Posted on 2008-10-27
2
743 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
2 Comments
 
LVL 1

Accepted Solution

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

828 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