Solved

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

Posted on 2008-10-27
2
739 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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now