SQL Server 2000 I/O Configuration in a SAN/NAS Environment
Some of the most common SQL Server performance problems involve the I/O subsystem. Since SQL Server’s main function is to manipulate data, and that data resides either in memory or on the I/O subsystem, any I/O performance problems will result in SQL Server performance problems. Much of the design of the SQL Server RDBMS is intended to make accessing the I/O subsystem as efficient as possible.
In this article the fundamental concepts and tuning of an I/O subsystems will be explored. By understanding the limitations of the I/O subsystem, you will be able to design and properly size it so that performance can be optimized. This article will start by describing the basics of how a disk drive works and the limitations of a disk drive. Next, RAID subsystems will be described and how to properly configure and optimize them. Finally advanced I/O subsystems such as SAN and NAS storage will be covered.
If you understand how SQL Server and the I/O subsystem interact, you can better configure your I/O subsystem for optimal performance. A properly configured I/O subsystem will allow SQL Server to perform optimally. A poorly configured I/O subsystem can easily become a bottleneck and can severely affect performance.
Why is I/O Performance Important?
The performance of the I/O subsystem is key to SQL Server performance. Further, read performance is critical to SQL Server performance, write performance is secondary. When a query is executed, the user waits on reads to complete before the system responds with the data that is requested. When modifications are done to the database, the lazy writer will write that data out at a later time, so, although write performance is important, no users ever wait on writes to occur (except to the transaction log). Let’s look at a couple of examples:
Indexed reads When a query is able to read from an index, it must make many trips to the I/O subsystem. The root page of the index is read and SQL Server makes a decision what next page in the index is read. That page then incurs an I/O and SQL Server again must make a decision and read again. With an efficient index that is not too deep, this might take 50-100 I/O’s in order to find the data. Let’s say for example that each I/O takes 10 ms (milliseconds). This index access will take 500ms – 1sec. That’s pretty quick.
Let’s now assume that the I/O subsystem is performing poorly, say each I/O takes 40ms. (I’ve seen as high as 450ms in production environments). In this case, the same index lookup would now take 2sec – 4sec. This is bordering on unacceptable, but this is often the case.
Table Scans Now let’s look at the case of the table scan. Here you will not be generating tens or hundreds of reads, but 10,000 or 100,000 or 1,000,000’s of reads. In this case, SQL Server is more efficient and will read multiple pages at a time, but queries that would take 10s of seconds on a well-tuned I/O subsystem could take 100s of seconds. A difference of 4x in read performance is quite noticeable.
This is why we are very concerned about I/O performance when we are tuning a SQL Server system. When a query takes excessive time to read due to a poorly tuned I/O subsystem, it may not only be affecting itself, but may be blocking other users as well. This leads to blocking and even to deadlocks. So, a well tuned I/O subsystem is critical.
In order to properly tune the I/O subsystem you must first learn about what affects the performance of the I/O subsystem. The smallest distinct piece of the I/O subsystem is the disk drive. The performance is also affected by your choice of RAID level. In this article you will see why this is so, and how to tune it.
Disk Drive Performance
A standard 15,000 RPM disk drive is a finite component, only able to perform a finite number of IOPS (I/O’s Per Second) without experiencing performance problems. This performance limitation is basically caused by the number of seeks per second that the disk drive can do. A top of the line disk drive takes approximately 6 ms (milliseconds) on average to move from where the disk heads currently are to where the desired data is. This information is based on disk drive specifications and experimental data. The 6 ms seek latency corresponds to 166 IOPS. However, when you get near to this limit, disk queuing occurs and latencies can increase exponentially. This is shown in the graph below.
In order to keep I/O performance reasonable, you should not exceed 75% of the maximum capacity of the disk drive (166 IOPS), which is approximately 125 IOPS. So, I/O tuning can be related to sizing. If your disk drives are configured to run within the specified limits, performance problems will be reduced.
If the number of IOPS issued to each disk drive exceeds the capacity, the latencies (response time) will increase. In fact, in practice it is not uncommon to see the normal 10ms-20ms latency increase to 40ms, 100ms or even worse in overloaded disk subsystems. These extreme latencies can significantly affect performance.