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
https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
SQL Server I/O Basics – Chapter 2
SQL Server Always Storage Solution Review Program
· https://www.microsoft.com/sql/AlwaysOn
Certification Policy
· KB913945- Microsoft does not certify that third-party products will work with Microsoft SQL Server
· KB841696 - Overview of the Microsoft third-party storage software solutions support policy
· KB231619 - How to use the SQLIOStress utility to stress a disk subsystem such as SQL Server
Fundamentals and Requirements
· White paper- SQL Server 2000 I/O Basics (applies to SQL Server versions 7.0, 2000, and 2005)
· KB230785 - SQL Server 7.0, SQL Server 2000 and SQL Server 2005 logging and data storage algorithms extend data reliability
· KB917047 - Microsoft SQL Server I/O subsystem requirements for the tempdb database
· KB231347 - SQL Server databases not supported on compressed volumes (except 2005 read only files)
Subsystems
· KB917043 - Key factors to consider when evaluating third-party file cache systems with SQL Server
· KB234656- Using disk drive caching with SQL Server
· KB46091- Using hard disk controller caching with SQL Server
· KB86903 - Description of caching disk controls in SQL Server
· KB304261- Description of support for network database files in 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
· KB298402 - Understanding How to Set the SQL Server I/O Affinity Option
· 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
· KB910378 - Scalable shared database are supported by SQL Server 2005
· MSDN article - Read-Only Filegroups
· KB156932 - Asynchronous Disk I/O Appears as Synchronous on Windows NT, Windows 2000, and Windows XP
Diagnostics
· KB826433 - Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
· KB897284 - SQL Server 2000 SP4 diagnostics help detect stalled and stuck I/O operations (applies to SQL Server 2005)
· KB828339 - Error message 823 may indicate hardware problems or system problems in SQL Server
· KB167711 - Understanding Bufwait and Writelog Timeout Messages
· KB815436 - Use Trace Flag 3505 to Control SQL Server Checkpoint Behavior
· KB906121 - Checkpoint resumes behavior that it exhibited before you installed SQL Server 2000 SP3 when you enable trace flag 828
· WebCast- Data Recovery in SQL Server 2005
Known Issues
· KB909369 - Automatic checkpoints on some SQL Server 2000 databases do not run as expected
· KB315447 - SQL Server 2000 may be more aggressive with Lazy Writers than SQL Server 7.0
· KB818767 - Improved CPU Usage for Database Logging When Transaction Log Stalls Occur
· KB815056 - You receive an "Error: 17883" error message when the checkpoint process executes
· Support Assistance (https://www.microsoft.com/sql/support)
Utilities
· 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)
Bob Dorr
Senior SQL Server Escalation Engineer