As AZ Jim reminds us in our comments, the configuration of your disk systems matter in more ways than one. There are many ways a controller can build RAID drives, consisting of at least two disks that perform as a single disk. The more disks participating in a RAID system, the better the fault tolerance should one or more drives fail. In some cases, it even improves performance.
This is an old topic, and there are many great resources you can find with a simple Google search. The reason I even bring up the topic today is that with modern hardware, the physical disks supporting your database, and the way they are configured, is starting to have less value on your database server. There are number of reasons why this is the case. Most of them don’t apply to a small business. In an enterprise where more cash is available, there are other options besides building the best, customized disk storage system for your individual database needs.
Let’s talk practical for a moment. If cost was not an option, you could totally optimize your database by having many different files for tables, indexes, and transaction logs. Each additional different disk reduces the amount of contention for when you need to access multiple database objects concurrently. If you are doing an index scan on a non-clustered index to find the records you need from a table, your query can work faster if it can read both the index, and the table at the same time. By putting the table on one file, residing on a completely separate disk from the index of the table, both can be read concurrently without having to move the head on the disk to point to the correct location where the data is located.
If you think about it, that kind of thinking is really cool. On the other hand it is completely impossible. To really work you would need to have a disk for every table and index in your database in order to eliminate all contention for disk resources. The problem is even bigger if you think about having more than one database hosted on the same physical disks.
One thing we do know that can be done practically is to separate log files from data files, placing them on completely different spindles. That is because they are accessed in different manners as we discussed yesterday. Yes, the underlying RAID configuration can make a difference; in most cases.
Today you can improve your database performance while still having a less than optimal disk system. Memory is a great equalizer. Compared to the cost of a optimized SAN you can drop a boatload of memory in your SQL Server. This is where the work on your database is performed in the first place. It is published to disk after all the hard works is done. The more memory you have, the better your performance will be. With lots of memory you have the option of using Memory Tables in SQL Server, increasing your performance even further.
Solid State Disks (SSD) have improved to the point that they are reasonable in a server, and last a much longer time that the early models. With SSD you could simply to a mirrored raid and have better performance than any configuration using hard disks. If you really need to spend money you can purchase a SAN consisting of fast (SSD), intermediate (high speed RAID volumes) and slow disk features. Some even move your data around automatically based on how often the data is accessed. Add cache to your SAN, and the disk configuration has less impact that just a few years ago.
One thing to keep in mind is the fact that, until your data is written to disk, it isn’t really part of the database. So, be sure that whatever disk subsystem you are using, it retains whatever is in write cache until the cache has been written to disk completely. A caching controller has to have a battery backup assuring if power is abruptly cut for some reason that the data in flight will not be lost.
There’s some disk performance ideas to get you thinking for today.
Cheers,
Ben