SQL Server Reference Guide

One of the most important concepts in SQL Server, or technically for almost any database platform, is storage. Usually abbreviated I/O, for Input/Output, this component is central to your server’s performance, security, integrity and data safety.

In a database system, storage isn’t just an advantage; it’s the heart of the system. In fact, few other hardware components have a greater affect on your system’s capabilities and performance than where and how the system ultimate places data. But I’ve been to many shops where the storage for a database system is given less thought than the amount of memory or the CPU speed, and I’ve talked to several folks for whom the way SQL Server uses files is a mystery.

It seems fairly obvious that as users add data, the storage space on your server grows. That’s true — but how do you plan for it? It also seems obvious that as the storage space becomes larger, things will slow down. But that isn’t always true. So what physical factors affect how quickly and efficiently the space is used? In this tutorial, we’ll cover the basics of those physical aspects of SQL Storage, and some of their effects on database performance.

In this overview I’ll explain a little about the physical drive subsystem and how SQL Server uses files. In other tutorials in the Database Guide I’ll explain those operations in more details. If you’re new to this topic or you have a co-worker in the storage-side of the IT team, this tutorial is for you.

Storage Connection Types

There are essentially two basic kinds of storage for SQL Server: Locally or Remotely attached. In fact, many servers have both internal storage and are connected to another storage subsystem.

Locally Attached Storage (sometimes called Direct Attached Storage Disks or DASD) is defined as the hard drives that are directly connected to the motherboard in the computer. The interface to these drives is called a Host Adapter (HA) or Host-Bus Adaptor (HBA), since the hard drive contains the actual controller. There are various specifications of this interface, such as Integrated Drive Electronics (IDE), Enhanced IDE (EIDE), and so on, and these specifications are part of the Advance Technology Attachment (ATA or ATAPI) specification, either Serial-ATA (SATA) or Parallel-ATA (PATA).

Server-level computers often use another type of directly attached specification called the Small Computer System Interface, or simply SCSI (pronounced "skuzzy"). This type of drive connects to the server through a wide variety of interfaces, from a locally installed SCSI card directly on the computer’s main bus through a new TCP/IP-based connection called iSCSI.

The second type of storage is Remotely attached. This refers to the placement of the drives outside of the server’s enclosure, and most often this takes the form of a Storage Area Network, or SAN. In a SAN, one or more servers have a Host Bus Adapter (HBA) installed, which is a processor board on the bus of the server that connects to a network of storage units, whether they are hard drives, CD or DVD libraries, or tape drives. This connection is normally made over fiber channel networks, which are very fast. All of this technology taken together is the SAN.

The drives, depending on the vendor’s terminology, are stored in enclosures or cabinets. Inside the cabinets are often cages, which have one or more trays, which contain one or more drives. As you can see, the granularity can be pretty fine, although for some vendor’s products drives are stored directly within a single enclosure.

The reason a SAN is so different has to do with the way the enclosures are connected to a server and the amount of servers that can be connected. In a SAN, you can think of the entire storage element as a separate computing resource from the server. In Locally attached storage, a drive connects only to one system. In a SAN, the enclosures (and ultimately the drives they hold) can connect to several servers.

Although the lowest physical level of units in a SAN is the drive, the firmware and software that controls the SAN allows the storage administrator to group drives together and present them to the operating systems through the HBA as a Logical Unit Number, or LUN. This way three 100 GB drives can be bunched together to appear as one 300 GB drive to the operating system, without making the operating system handle the overhead of the grouping.

Hard Drives

At the physical drive level, whether Locally or Remotely attached, the drive mechanics are essentially the same.

A computer’s hard drive is a set of magnetically charged platters that are spun by a motor at a constant speed. A set of arms (one per platter) with a head mechanism at each end (think of a record-player, if you’re as old as me) floats over the platters. A computer logic board on the bottom of the drive directs the heads to a specific location on the platters and measures the magnetic charge at that spot, returning or setting a data bit on the platters. (See how it works at this IBM Research site.)

If you imagine each platter as a pie, then the slices of pie are called sectors. When you spin a disc, the inside parts of the disc move past a fixed point quicker than the outside parts. One edge of the drive has to be wider to allow for this slower read. Because of this behavior, much of a drive’s surface is actually wasted.

That’s the really fast, simplified view of how drives work. You can see that several factors affect the speed of data writes and reads, such as spin rate (called the RPM speed) and how quickly the arms move into position (called seek time). As far as SQL Server is concerned, faster is better.

The next concept to understand is the interface to the drive. The two main standards today are EIDE and SCSI. There are sometimes some fairly obvious speed differences between the two, but there’s more to it than that. You can read more about it on this web site.

For speed and reliability, it’s better to have more disks. Separating things like the Operating System, Data Files, Log Files, Index Files and the tempdb database allows multiple operations to happen at the same time, which speeds things along. Note that I’m talking about a physical separation between these objects, not just a different drive letter. If you have a large drive and divide it up into different drive letters, you’re not going to work any faster than if everything was just on drive “C:”.

When multiple disks are arranged in certain patterns and use a specific controller, they can form a Redundant Array of Inexpensive Disks (RAID) set. There are several numbers associated with RAID, but the most common for database systems are 1, 5 and 10.

RAID 1 works by duplicating the same writes on two hard drives. Let’s assume you have two 20 Gigabyte drives. In RAID 1, data is written at the same time to both drives. RAID1 is optimized for fast writes.

RAID 5 works by writing parts of data across all drives in the set (it requires at least three drives). If a drive failed, the entire set would be worthless. To combat this problem, one of the drives stores a "parity" bit. Think of a math problem, such as 3 + 7 = 10. You can think of the drives as storing one of the numbers, and the 10 is the parity part. By removing any one of the numbers, you can get it back by referring to the other two, like this: 3 + X = 10. Of course, losing more than one could be evil. RAID 5 is optimized for reads.

RAID 10 is a bit of a combination of both types. It doesn’t store a parity bit, so it’s fast, but it duplicates the data on two drives to be safe. You need at least four drives for RAID 10. This type of RAID is probably the best compromise for a database server. You can read a lot more about RAID on RAID.com.

SQL Server and the Storage Subsystem

So how does all this fit in with SQL Server? Well, you need to make sure your servers have the fastest drives possible, and have the proper RAID configuration.

SQL Server keeps data stored in a logical area called a Filegroup, and log files are stored separately. If you create a database, and then start adding data to it, you’ve actually done several things at once.

First, you’ve created a new Filegroup. If you don’t specify otherwise, you’ve got one called “Primary” and it is the DEFAULT filegroup — all the tables and indexes that you create from then on will go onto “Primary”. Second, you created a new logical file so that the system can find the data. Third, you created a physical file on the hard drive (with an extension of MDF or NDF) where everything actually sits. Finally, you created a logical and physical log file. All that from a simple CREATE DATABASE statement!

So now you create a table and an index, and it all looks like this:

Figure 1

Now, let’s make it a bit more interesting. With the ALTER DATABASE statement, you can add another file to the same Filegroup. You could even put the physical file it uses on another drive — speeding up things. Here’s what that looks like now:

Figure 2

Notice that the tables and indexes just “spread” across the files. You can’t specify (at this level) where the data goes, it just balances itself across the files in the filegroup. For the log file, the writes go from the bottom of the file to the top, and then on to the next log file if you have one.

Figure 3

Now you can use the ALTER DATABASE statement and add another Filegroup. At this point, it doesn’t have any files in it at all; it’s just a logical container. After that, you can add a new file to that Filegroup, and when you create a new table you can specify the “ON FileGroup2” (or whatever you named the Filegroup) to put data or indexes there.

Figure 4

Notice I also added another log file, although this doesn’t help with performance very much, since it won’t balance the writes like a Filegroup.

But, because I have two Filegroups, the writes on “Primary” and “FileGroup2” can happen independently, making everything faster. And that forms the basis of tuning the I/O subsystem.

Figure 5

Another tuning feature is to match up the drive subsystem with how SQL Server is writing data. The log is written to in a sequential fashion, since all writes, deletes, or edits normally go through the log. All reads normally go through the database. For that reason, it’s best to separate the physical drives used for the logs and the database. Your server will then be able to write log entries and read data at the same time.

Suppose you have a database with a heavily used table, which also contains a fairly wide (many columns) index. That makes the table and index compete for the same access on the hard drive, slowing down the total access time. In this case, it makes sense to separate out the physical files that the table and the index use, and to place them on separate drives.

So how does SQL Server allocate and store that data? SQL Server’s storage is arranged in pages and extents. A page is 8KB of data (128 pages per megabyte), and an extent is 8 pages (16 extents per megabyte). A row can’t be larger than 8060 bytes. If you do the math, you’ll notice that 8060 isn’t 8KB. That’s because there is some overhead involved in storing the header.

The database can be set to grow automatically, by either a percentage of the size of the database or a set number of megabytes. I normally choose the percentage, since as the database grows larger it has to reallocate space less often. Your mileage will vary.

Databases can also shrink automatically. I don’t use this setting, because I’ve noticed a DBCC command running throughout the day to accomplish the task. I normally just let my maintenance plans take care of shrinking the files during maintenance. Also, shrinking the file almost guarantees that the indexes will become fragmented, which is a bad thing.

As another consideration, it’s best to have as many spindles in a database drive setup as possible. Microsoft Windows and SQL Server both allocate threads from the operating system based on the number of physical drives you have, so the more the better. Also, separating your files (based on the read/write patterns they have) allows the drives to be spinning at the same time for a write as a read. That way the drive doesn’t have to wait until a write completes to do a read, or visa-versa.

So to recap, the way you want to arrange your storage is highly dependent on the type of attachment and drives that you have. At a minimum, I recommend that you split the operating system and the program files for SQL Server from the page file onto separate physical drives (not just drive letters), place the database files on another physical drive, and use yet another physical drive for the log files. Even better is to use another physical drive for the indexes that you set up, so that the indexes can update their values as the table data is written.

Books and eBooks

If you need to go hard-core with direct-attached storage, the book PCI System Architecture, 4th Edition is pretty intense.

There’s actually a link between storage and consolidation. You can read more about that in Foundations of Green IT: Consolidation, Virtualization, Efficiency, and ROI in the Data Center, by Marty Poniatowski.

Online Resources

Here’s a great whitepaper from Microsoft with an huge amount of information on the physical database files.