What do I need to know about SQL Server database engine I/O?
Several years ago I started a whitepaper to describe the basic I/O requirements for the SQL Server database engine. This continued to grow and has become a series of chapters. Chapter 2 is a continuation from the original whitepaper so be sure to read them as chapter 1 and chapter 2.
I wanted all vendors, customers, support professionals, and other individuals to have a very clear understanding of how SQL Server performed database engine I/O and the attributes a subsystem needed to supply to maintain the ACID properties of the database.
These whitepapers play a key role in the SQL Server Always On Solution Reviews Program. They have also been used as key reference materials for various training sessions as well as PASS presentations. Anyone installing a SQL Server should review these and the links below to better understand the I/O system requirements of SQL Server.
SQL Server 2000 I/O Basics
SQL Server I/O Basics – Chapter 2
SQL Server Always Storage Solution Review Program
Fundamentals and Requirements
· White paper- SQL Server 2000 I/O Basics (applies to SQL Server versions 7.0, 2000, and 2005)
· KB231347 - SQL Server databases not supported on compressed volumes (except 2005 read only files)
· KB234656- Using disk drive caching with SQL Server
· KB910716 (in progress) - Support for third-party Remote Mirroring solutions used with SQL Server 2000 and 2005
· KB833770 - Support for SQL Server 2000 on iSCSI technology components (applies to SQL Server 2005)
Design and Configuration
· White paper - Physical Database Layout and Design
· KB78363 - When Dirty Cache Pages are Flushed to Disk
· White paper - Database Mirroring in SQL Server 2005
· White paper - Database Mirroring Best Practices and Performance Considerations
· MSDN article - Read-Only Filegroups
· KB897284 - SQL Server 2000 SP4 diagnostics help detect stalled and stuck I/O operations (applies to SQL Server 2005)
· KB167711 - Understanding Bufwait and Writelog Timeout Messages
· WebCast- Data Recovery in SQL Server 2005
· Download - SQLIO Disk Subsystem Benchmark Tool
· Download - SQLIOStress utility to stress disk subsystem (applies to SQL Server 7.0, 2000, and 2005 - replaced with SQLIOSim)
Senior SQL Server Escalation Engineer