Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 78

By examining various storage hardware components, you will learn how best to protect your data
with RAID technology. You will also see how storage area networks assist in data protection.
Finally, you will learn how to validate your functional coni guration and performance.
SQL SERVER I/O
Let's begin by investigating how SQL Server generates I/O. We are concerned with reading existing
data and writing new data. At its most basic SQL Server is made up of a few i les that reside within
the server i le system. As a rule, different computer system components perform at different rates.
It is always faster to process items in the CPU than it is to serve requests from processor cache. As
detailed in the hardware chapter, L2 and L3 CPU cache is faster than computer memory. Server
memory is faster than any I/O component.
SQL attempts to mitigate the relatively slow I/O system by caching whatever it can in system
memory. Newly received data is i rst written to the SQL transaction log by SQL Server write-ahead
logging (WAL) as you saw in Chapter 1. The data is then written to buffer pages hosted in server
memory. This process ensures that the database can be recovered in the event of failure.
Storing the buffer pages in memory ensures that future reads are returned to the requestor promptly.
Unfortunately, server memory is not ini nite. At some point SQL server will need to write data.
In Chapter 1 you learned about how SQL Server writes data to disk using the Lazy Writer and
Checkpoint processes. This chapter will cover the mechanics of how the operating system and
storage subsystems actually get this data onto disk storage.
Contrast these write operations with read requests that are generated by SQL Server worker
threads. The workers initiate I/O read operations using the SQL Server asynchronous I/O engine.
By utilizing an asynchronous operation worker threads can perform other tasks while the read
request is completed. The asynchronous I/O engine depends on Windows and the underlying storage
systems to successfully read and write data to permanent storage.
SQL Server takes advantage of the WriteFileGather and ReadFileScatter Win32 APIs.
WriteFileGather collects data from discontinuous buffers and writes this data to disk.
ReadFileScatter reads data from a i le and disperses data into multiple discontinuous buffers.
These scatter/gather APIs allow the bundling of potential I/O operations thus reducing the actual
number of physical read and write operation.
Understanding Windows storage is the key to tuning SQL Server I/O performance and guaranteeing
data integrity. This chapter aims to arm database administrators and designers with the appropriate
nomenclature to enable communication with other information technology disciplines. Open, reli-
able communication is the ultimate key to successful relational data management.
STORAGE TECHNOLOGY
The Host Bus Adapter (HBA) handles connections from the server to storage devices and can also
perform several other roles. While a basic HBA provides connectivity to storage, more advanced
HBAs have embedded Array controllers. When the storage is located within or attached to the