Planning the SQL Server Disk Layout

3 4

As you have seen earlier in this chapter, you should configure your I/O system properly in order to avoid overloading it. Overloading the I/O subsystem will cause the I/O latency to increase and degrade SQL Server performance. In this section, you will learn how to build a SQL Server system that can perform within the limitations of your subsystem. The first part of this configuration exercise shows you how to determine the I/O requirements of your system. Then you will plan your system, and finally you will create your system.

Determine I/O Requirements

Determining the I/O requirements of a system that exists only as a concept can be difficult, if not impossible. However, if you can't determine the I/O requirements from hard data, you might be able to gather enough data to make an educated guess. In either case, building an I/O subsystem that cannot expand is not a good idea. Always leave some room for required increases in capacity and performance because sooner or later you will need them.

You should design your system to meet a set of minimum requirements based on the amount of space that you need for data storage and on the level of performance you need. In the next sections, you will see how to determine how many disks these factors require.

Space

The process of determining how much space your database requires is fairly straightforward. The amount of space is equal to the sum of the following:

• Space required for data
• Space required for indexes
• Space required for temporary data
• Space required for the transaction log

The space required for data must include enough space to handle data that is added to your database. Your business and your customers will dictate, to a large degree, the amount your database will grow. To determine what your system's growth rate is, you can check your existing database on a regular basis and calculate the size differences in the amount of space used in the database. This growth rate should be calculated over several months in order to determine trends. You might be surprised by the rate at which your data is growing.

In a system without any history, you can estimate the amount of growth by taking the number of product orders, among other things, and multiplying that by the estimated row size. Doing this for several periods (perhaps months or years) will give you a rough idea of the rate at which the data files will grow. This will not tell you how much your indexes will grow. The amount of index space per data row depends on how the index is constructed and on the amount of data. A complex index takes more space per row of data than a simple index. It is then up to you to determine whether your system should be able to handle growth for two years, five years, or longer. This will allow you to determine how to configure your I/O subsystem for space.

Once you have determined the amount of data in the database, the size of the indexes, the amount of temporary database space required, and the rate of growth, you can determine how much disk space is required. You must then take into account the effects of using RAID fault tolerance. Remember, RAID 1 or RAID 10 (data mirroring) takes up half the disk space of the physical disk drives. RAID 5 takes up the disk space of one disk of the array. Also, remember that the disk size that the manufacturer provides is unformatted space. An unformatted disk drive that is labeled as a 9.1-GB disk is actually an 8.6-GB disk when formatted.

So once you have calculated the amount of space currently required as well as the amount of growth space required, you must then move to the next step: performance. It is necessary to calculate both space and performance requirements and configure your I/O subsystem accordingly.

Performance

It is not sufficient to simply configure your system to meet the space requirements. As you have seen throughout this chapter, how you configure the I/O subsystem can severely degrade or significantly enhance the performance of your system. However, determining the performance requirements of your I/O subsystem is not nearly as easy as determining the space requirements.

The best way to determine performance requirements is to look at a similar application or system. This data can give you a starting point to use in estimating future requirements. You will learn much more about this in Chapter 6. Assuming that you find a similar system, you can then use data that was gathered from that system and the information given earlier in this chapter to determine the number of disk drives required. Remember to take into account the RAID level that will be used on that I/O subsystem. The next steps are to plan the SQL Server disk layout and then to implement the solution.

Plan the Disk Layout

Planning the layout involves determining where the data will be positioned and then creating SQL scripts to create the database. The advantage of creating databases with SQL scripts, rather than through SQL Server Enterprise Manager, is that you can reuse a script, modifying it if necessary.

The script should take into account the number of logical volumes that your system has, as well as the number of physical disks in those volumes. It is important to balance the database so that each disk drive will handle roughly the same number of I/O operations per second. An unbalanced system will suffer the performance experienced by the slowest volume. You should make sure that the transaction log and the data files are distributed across the disk drives in a way that supports optimal performance.

Planning the Log

The process of planning where to put the transaction log is fairly simple. Using only one data file for the transaction log is often the best approach. If you must add more log files to the database, be sure that they are placed on a RAID 1 or RAID 10 volume. Also, be sure to isolate the transaction log from data or other transaction logs.

Planning the Data Files

The easiest way to configure the I/O subsystem for the data files is to configure each volume with a similar number of similarly sized disk drives. In many cases, you don't need to split the I/O subsystem into multiple volumes. In fact, you might be perfectly happy with one logical volume that spans the entire controller. However, you shouldn't use Windows 2000 striping to span multiple controllers because it adds too much overhead.

Tip

---

For your data files, span as many disk drives per controller as you can. This will allow the controller to distribute the data among multiple disks. Do not use Windows 2000 striping to span multiple controllers. This incurs too much CPU overhead.

If you use multiple controllers, you should simplify the configuration by using similar striping with the same number of disk drives on each controller. If you can't use the same number of disk drives on each of your controllers, you can use proportional filling to properly populate the database.

For example, if you use two volumes, one with 20 disk drives and the other with 10 disk drives, you should create a filegroup with two data files. (You will learn more about using files and filegroups in Chapter 9.) The first data file should go on the 20-disk volume and be twice as big as the data file on the 10-disk volume. As data is loaded, SQL Server will load twice as much data into the first data file as it loads into the second data file. This should keep the I/O load per disk drive approximately the same.

Implement the Configuration

Once you have developed your SQL scripts to create the database, it is necessary only to run them and to view the result. If you made a mistake and the database was not created as planned, now is the time to fix it, not after the data has been loaded and users are accessing the system. The use of SQL scripts allows you to modify the scripts and to run them again and again as necessary. An example of a script that uses multiple files within a filegroup to spread the database among several controllers is shown here.

  -- -- SQL script to create a database over several files -- d:, e:, and f: for data. e: and f: have twice the number -- of disk drives as d:, so they get allocated twice the  -- database size as d:. l: is used for the log. -- CREATE DATABASE demo  ON PRIMARY ( NAME = demo1,    FILENAME = `d:\data\demo_dat1.mdf',    SIZE = 100MB,    MAXSIZE = 200,    FILEGROWTH = 20), ( NAME = demo2,    FILENAME = `e:\data\demo_dat2.ndf',    SIZE = 200MB,    MAXSIZE = 200,    FILEGROWTH = 20), ( NAME = demo3,    FILENAME = `f:\data\demo_dat3.ndf',    SIZE = 200MB,    MAXSIZE = 200,    FILEGROWTH = 20) LOG ON  ( NAME = demolog1,    FILENAME = `l:\data\demo_log1.ldf',    SIZE = 100MB,    MAXSIZE = 200,    FILEGROWTH = 20) GO 

The information in this chapter, especially in this section, should help you to create an optimal I/O subsystem for your SQL Server system. The next section contains several tips and recommendations to help you with both creating and fixing subsystems.