SQL Server I/O

When running on a member of the Windows NT family, SQL Server performs as much file I/O as possible asynchronously. You'll recall from Chapter 5 that the standard Win32 API functions can be executed asynchronously when the file object they're working with has been opened with the FILE_FLAG_OVERLAPPED switch and a pointer to a valid OVERLAPPED structure is passed in to the functions that require it. For example, if you call CreateFile and pass in FILE_FLAG_OVERLAPPED, then call ReadFileEx with the returned file handle and a pointer to a valid OVERLAPPED structure, Windows will attempt to process your request asynchronously. SQL Server takes advantage of this Windows' facility to avoid blocking on I/O whenever possible.

There are situations, of course, that prevent SQL Server from processing I/O asynchronously. One obvious one is running it on a member of the Win9x family. Because Win9x doesn't support asynchronous file I/O, all SQL Server file I/O on Win9x is performed synchronously. SQL Server's UMS component is responsible for handling the scheduling of I/O requests and has special code to detect Win9x and perform I/O synchronously. See Chapter 10 for more information on UMS and its processing of asynchronous I/O.

Another example of a situation in which SQL Server can't use asynchronous I/O is when the MDF and LDF files that make up a database have been compressed using NTFS file compression. As I mentioned in Chapter 5, Windows prohibits the use of asynchronous file I/O against compressed files and will either turn any asynchronous request against such files into synchronous operations for those APIs that support synchronous I/O (e.g., ReadFile) or return an error for those that don't (e.g., ReadFileEx). This is one reason that Microsoft doesn't support the compression of database files.

SQL Server makes use of scatter-gather I/O in order to quickly load a contiguous disk file region into a set of buffers that may or may not be contiguous in memory. As we saw in Chapter 5, the ReadFileScatter and WriteFileGather Win32 API functions take a pointer to an array of I/O buffers, then either load data from disk into them or write data from them to disk. By supporting noncontiguous source and destination memory buffers, these API functions allow SQL Server to avoid having to use a contiguous intermediary buffer that matches the size of the disk file region being read or written to and copying it to or from a series of noncontiguous memory buffers. First introduced in a service pack for Windows NT 4.0, scatter-gather I/O allows SQL Server to achieve better scalability and greater performance than would otherwise be possible. It allows the BPool and MemToLeave memory managers to do what they do best—manage large numbers of noncontiguous buffers without having to be concerned with locating any of them in particular proximity to the others based on I/O requirements—while still allowing the storage engine to process I/O as quickly as possible. See Chapter 5 for more information on scatter-gather I/O.