SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 3. Disk Subsystem Performance

SQL Server is a very I/O-intensive application: it is constantly reading data from and writing data to disk. Good I/O throughput is essential for SQL Server performance and health. Unfortunately, many SQL Server installations are I/O bound, even with modern flash-based storage.

In this chapter, I will show you how to analyze and troubleshoot disk subsystem performance issues. You will learn how SQL Server processes I/O requests internally and how to identify and detect possible bottlenecks through the entire I/O stack, on the SQL Server, OS, virtualization, and storage levels.

Next, I will talk about checkpoint process tuning, a common source of I/O bottlenecks in busy OLTP systems.

Finally, I will cover the most common I/O-related waits you may encounter in your system.

Anatomy of the SQL Server I/O Subsystem

SQL Server never works with data pages directly in database files. Every time a data page needs to be read or modified, SQL Server reads that page to memory and caches it in the buffer pool. Each page in a buffer pool is referenced by a buffer structure, sometimes simply called a buffer. It includes the page’s address in the data file, a pointer to the data page in the memory, status information, and the page latching queue.

SQL Server uses latches to protect internal objects in memory, preventing them from corruption when multiple threads are modifying them simultaneously. The two most common types of latch are exclusive, which blocks ...