by Bill Wunder

As impressed as I am that SQL Server 2000 Books Online is the most complete and informative help system I have ever had the pleasure of working with, it's fascinating to me that for even the most rudimentary and somewhat antiquated disk hardware guidelines you have to drill deep down into the Optimizing Database Performance Chapter. Seems to me that getting the right disk subsystem fits right at the beginning of the Installing SQL Server Chapter because if you don't start out with an at least adequate IO subsystem you're either not going to like the way SQL Server performs or your database needs could have been filled with Access, MSDE or MySQL for a much lower cost than SQL Server.

Some time ago in the SQL Server Magazine UPDATE weekly email newsletter I saw an interesting poll concerning the number of processors on the SQL Server machines that folks are running. The results are interesting because they suggest that most people are probably not running a heavy iron machine as I tend to imagine each time I face a scalability issue that can be solved by "throwing hardware at it". The poll indicated that 55% of the 256 respondents were running on a two processor machine. 26% were running on 4 CPU hardware, 14% on single processor machines and 6% were using 8 - or more I presume - CPUs. To me these numbers paint a picture in which most of us are still using Direct Attached Storage Devices (DASD) rather than Storage attached Network (SAN) drives and most of us are running on machines with 4GB or less of memory. If true, that means that for most uses. a SQL Server that would be best served if Microsoft provided clear advice on how to configure the local attached DASD systems. This seem to me to be missing from Books Online and even www.microsoft.com/sql.

Sadly, if the September CTP documentation is any indication, SQL Server 2005 Books Online is no more helpful with regard to disk configuration guidelines than is SQL Server 2000 Book Online.

When you finally do get to the Physical Database Design sub-section of the Database Design section in the Optimizing Database Performance Chapter in SQL Server 2000 Books Online the information is not as spectacular and fresh as it is with so many other sections in Books Online. I'll pick on the RAID discussion as an example. I've never worked in a shop that would consider a RAID 3 configuration but at one point RAID 3 is included in the set of "typically implemented in hardware-based" RAID configurations, in another spot the documentation states that only "RAID levels 0, 1, and 5 are typically used with SQL Server" and on yet another page it declares that "RAID 10 provides the highest read/write performance" Even while in that same document somewhat misleadingly labeling RAID 10 "proprietary". That's some fairly different messages depending upon which page you use as a resource. Fact is I'm not at all certain I've ever used a controller that would support a RAID 3 configuration let alone actually tried a RAID 3 configuration - or RAID 2 or RAID 4 for that matter, but the documentation doesn't advertise those as typical so I won't rant too much on the small amount of space wasted on those RAID levels. And somewhat in agreement with the Books Online suggestion to contact the hardware vendor for specifics on RAID 10, I have seen confusing and erroneous information in lots of other places - including hardware vendor documentation - concerning RAID 1+0 and RAID 0+1 for example. Still, I would like to see Books Online present a consistent basic guideline.   

From my experience, all too often there is no one on staff with an adequate understanding of just how critical getting the correct disk configuration can be a successful database application. More likely you'll have someone that has a good understanding of RAID and someone else responsible for specification and building of the SQL Server and yet someone else expected to administer the database once it's up and running. The trick is to get those people collaborating early in the process. There is unfortunately a good chance if you followed the Books Online story line that you could end up with your databases on the boot disk coming out of the gate. More likely, there will be a realization that the boot device is not big enough for the monster database that everyone hopes is about to take life so there may at least be a single RAID set created for the database. Without a more appropriate initial configuration the next thing that's likely to happen is that poor performance will lead to a migration of the database transaction logs to their own RAID set. There's no clear guidance from Books Online which RAID level this log device ought to be, instead there is simply a couple of non-specific sentences suggesting that you might suffer a slow running SQL Server if you don't put the transaction log on it's own physical device. You are left to wonder if it should be RAID 0, 1, 3(?), 5 or 10 for the logs or even if SATA or EISA is a possibility or so we are left to surmise from the lack of detail presented. A third optimization that can be gleaned from the Books Online page about Placing Indexes on FileGroups indicates that , "By creating the index on a different filegroup, you can realize performance gains if the filegroups make use of different physical drives with their own controllers."  The page makes no gesture toward indicating how the different physical drives might be best configured, nor does it even recognize that some of today's multi-channel RAID controllers can provide performance similar to older systems with one channel per controller or less robust controller caching designs simply by using different channels on a multi channel controller rather than adding another controller (check with the vendor to be sure).

With all due respect to the good people at Microsoft that built Books Online, I believe the vague section on RAID in Books Online is the result of a deference by Microsoft to favor a particular hardware manufacturer or technology over another. Very politically correct I'm sure, but it sure doesn't serve the best interest of achieving optimal results with SQL Server. In my opinion that should get more weight in the equation than it does though probably not very PC of me to say so.

An off shoot of this reluctance of the SQL Server help system to properly and adequately educate us about how best to build a server that will result in a well performing SQL Server for the need at hand is that we must either decide which hardware vendor's spiel is most accurate or we must rely upon our peers in the forums, news groups, and list servers on the Internet to help us build a better server. I think I won't say much about vendor technical sales support and white papers here other than a recommendation to rely upon such information mostly for comparative analysis and standards based performance specifications rather than initial planning guidelines. Even the standards based metrics from different manufactures can be difficult to compare since different manufactures take different tact in collecting the data. Let's just suffice it to say I am perhaps excessively skeptical. If I'm out shopping for a car, you don't want to be the salesperson at the front of the queue when I come on the lot. I'd say my approach to hardware vendor sales literature is not much different. Lets face it, A hardware vendor is best served if they over sell you. You're not likely to complain if the disk subsystem is faster than you need and they will likely make more money with that system.

What we really need are some guidelines for optimal configuration of different classes of SQL Server installations. At this time, looking to the on-line communities provide the best opportunity for finding out what others have done with disk configurations for databases that must operate under duties similar to the one that you are about to build or are in need of making perform better. The challenge, even when turning to the peer community is to provide enough detail so others can provide information that is relative to your needs. Otherwise you're likely to get the same kind of overstated feed back from the community experts that you might get from a hardware vendor. But in defense of the generous, knowledgeable, and usually cautious experts, they are usually trying to provide a generalized and simplified answer to a complex problem when not enough information has been presented to give a  more specific recommendation. For example, I often see such advice as RAID 1+0 is the fastest but if you can't afford it use RAID 5.

There should be little disagreement that all other things being equal RAID 1+0 delivers the best overall performance, however it's really more than a simple financial situation that should drive server configuration decisions whether the funds are limited or the goal is to make a wise hardware selection. The base recommendation implies that you should never use anything but RAID 1+0 if money is not a limitation. Much more relevant are the performance requirements of the application, the perception of the users and the fiscal efficiency of the equipment expenditure in meeting those requirements and satisfying those perceptions. While I believe you could safely say that RAID 1+0 will write to disk faster than RAID 5 I'm afraid I have only rare confidence in a statement such as "...RAID 1+0 is better than RAID 5 for database servers." It is risky to equivocate or simplify this issue. While certainly disk IO is one of the most important performance consideration when building a SQL Server there are a myriad of ways to balance price/performance to meet expected requirements and only analysis, testing, and knowledge of the actual circumstances for a given SQL Server's situation can you get to the right choice. Syllogistically I could say that given the same fuel octane and road conditions a Maserati will go down the road faster than a Honda every time. The implication is that given an unlimited budget all the Honda Drivers would be better off with a Maserati. (Imagine a rush hour where everyone is driving an MC12.) The reality is that there are a lot more Hondas on the road than Maseratis, most people are quite satisfied with their Honda, even the Maserati will only go as fast as the person running the throttle tells it to go, and both are capable of much more speed than the rest of the driving infrastructure can support. It could even be said that the Maserati exceeds any real need for the typical driver and represents only a classic example of conspicuous consumption. (OK, I wouldn't mind driving one just once ;)

Similarly, no one should disagree that RAID 1+0 is statistically more redundant than RAID 5. You can loose two disks in RAID 1+0 set and not lose data. Even more in some situations with no loss of data. You can only loose one disk in a RAID 5 with no loss of data. (Note that even in a RAID 1+0 you can lose the wrong two disks and still need to restore from the last now good backup, so don't skimp on backup either!) Just to take a little more liberty with the auto analogy, another syllogistic implication is that a Maserati with a spare for each tire is more reliable than a Honda with only a cheesy space-saver spare. I would argue that in some ridiculously high percentage of cases, the single Honda spare is adequate to get you from point A to point B - reliably but perhaps not without some inconvenience. The real decision is whether that tiny window of difference is justified for your application. For some the answer is yes, for others there is no added value to the added degree of redundancy that RAID 1+0 provides (or a fancy two seater with spare tires bulging out the passenger side window).

Sorry I don't know from where, but I saved this comment in my notes and I am sure it is from a Microsoft sourced technical document that had some good general advice recently when looking for things to evaluate as my shop was building out a new SAN after the first SAN they selected and installed proved inadequate for our needs in terms of reliability and performance. That's another story, our topic here is DASD, but the comment is still very appropriate:

"We recommend RAID 1+0 for heavy write intensive operations (log & Tempdb).... However some RAID 5 implementations offer good performance and are more cost effective. Many vendors have whitepapers documenting their RAID 5 vs. RAID 1+0 performance and we recommend reviewing these as well as testing the performance of RAID before deploying to ensure IO performance requirements will be met."
 

You can tell it's a Microsoft comment because they are obviously deferring to hardware manufactures for more specific guidance, but the valuable point is that RAID 5 isn't just cheaper, it's more cost effective in many cases. The truth is I made a note of the comment because it mirrors my personal experience and the recommendations I see most frequently for DASD very closely.

So, what is my opinion?

Transaction logs tend to process pseudo-streams and do not benefit from a large memory footprint since the data needs to get disk immediately to assure recoverability. And the disk writes to the log are pseudo-streamed to the end of the log file. For these reasons transaction logs are usually best placed on a RAID 1. If you can't get enough log space out of a single RAID 1 mirrored pair I'd almost suggest adding another mirrored pair and splitting the log on two devices over a RAID 1+0, but the truth is I find I do not need more log space than I can get out of mirrored pair of drives. The is even more true these days when I can have 144GB on a mirrored pair, but I found the same to be true when all my DASD was 9GB ultra SCSI stuff.

tempdb? Well yes maybe you want it on a RAID 1+0, but only in a situation where tempdb is stressed and I'd suggest taking a hard look at your tempdb usage over having to pay for and support tempdb on it's own device. In a lot of cases effective indexes to support necessary joins are faster than heavily decomposed queries that make use of temp tables for example. If you determine that you do need to support intensive tempdb usage, reading between the lines in Book Online there is even the possibility that putting tempdb on it's own RAID 5 will improve performance assuming you need enough tempdb space to justify anything more than a RAID 1 mirrored pair to isolate tempdb. Books Online's recommendation is to place a stressed tempdb on a striped disk set. (Hopefully that suggestion doesn't lead anyone to put tempdb on a RAID 0 which meets the recommendation but puts the entire server at risk should only one disk fail.) I'm of the opinion that in most cases configuring tempdb on the same device as the user databases is going to be adequate. Avoid unnecessary complexity.

From what I am hearing, SQL Server 2005 is going to be even more tempdb intensive so tuning the hardware under this database is going to be even more critical. Row versioning, for example, as used in online indexing and snapshot isolation relies upon tempdb. 

Concerning data and indexes, I'd have to say there is more performance benefit in most cases to putting data and non-clustered indexes on separate RAID 5 devices than will be realized by putting either or both on a RAID 1+0. In general, for data and non-clustered indexes, only the most sensitive data and the most highly stressed disk systems are going to realize an advantage with RAID 1+0. Most of us are better off with RAID 5. You can get more usable storage from a RAID 5 in the same DASD cabinet. You can run more storage space and ultimately more servers in a data center with RAID 5 DASD. And of course you'll spend less money to store the same amount of data in a RAID 5. There are advantages to RAID 5 that have nothing to do with how much you can afford but have everything to do with how wisely you spend your money. Big difference.

And these days with U320 SCSI readily available and PCI-E just around the corner, the good news is that disk performance is likely to become less of an issue for more SQL Server's when a few basic configuration rule of thumb best practices that Microsoft seems reluctant to tell us are followed. So, to summarize, here are what I believe the IO subsystem best practice rules of thumb are for a successful, efficient and economical SQL Server:

•     Do not put SQL Server data devices on the boot disk
•     Put logs on a RAID 1 on an independent SCSI channel
•     Put data on a RAID 5 on an independent SCSI channel
•     If read disk queuing is high on your data device (avg 2 or higher per spindle) put non-clustered indexes in a new filegroup on a RAID 5 on an independent SCSI channel
•     If tempdb is stressed (consistent blocking in dbid 2 is one common indicator) and you cannot redesign to relieve the stress put tempdb on a RAID 1+0 on an independent SCSI channel and the tempdb log on yet another RAID 1+0 on an independent channel.   
•     If your database holds highly sensitive data consider RAID 1+0 over RAID 5.
•     Avoid unnecessary complexity (KISS).

Beyond those few guidelines there are some legitimate specialized configurations, but a rule of  thumb nor generalized configuration advice are not appropriate for those exceptional circumstances. If you find yourself in such a situation I suggest checking in with the experts in your favorite Internet community. If you do, be sure to provide the details of your situation. If you ask the question right, I'm sure you'll find just the right advise.

Bill