IO Latencies and SQL Server

3 4

SQL Server is especially sensitive to I/O latencies because of the concurrency of transactions within the SQL Server engine. Under normal conditions, tens or hundreds of applications are running against a SQL Server database. To support this concurrency, SQL Server has a complex system of row, page, extent, and table locks, as you will see throughout this book. When a piece of data or a SQL Server resource is locked, other processes must wait for that data or resource to be unlocked.

If I/O operations take excessive amounts of time to complete, these resources will be held for a longer period than normal, further delaying other processing in the system. In addition, this could lead to a greater chance of deadlocks. The longer the I/O takes to complete, the longer the locks are held, and the higher the potential for problems. As a result, individual delays can multiply into a "snowball effect" that cripples the system.

In addition, query processing will be significantly slower. If long table scans are running on your system, for example, hundreds of thousands or even millions of rows will often need to be read in order to complete the task. Even slight variations in performance become dramatic when applied to a million I/O operations. One million operations at 10 ms each will take approximately 2.8 hours to complete. If your system has overloaded the I/O subsystem and each I/O operation is taking 40 ms, the time to complete this query will increase to more than 11 hours.

As you can see, SQL Server performance can be severely degraded by a poorly sized or poorly configured I/O subsystem. By designing your I/O subsystem to work within the capacity of the individual components, you will find that your system's performance will be optimal.