《MicrosoftSQLServerDatabaseEngineIO》.docx

Microsoft SQL ServerDatabase Engine I/O by Bob Dorr, Microsoft SQL Server Principle Escalation Engineer, 1994 – PresentBuilt: Jan 2008 Areas Covered Write Ahead Logging (WAL) Protocol Synchronous vs Asynchronous I/O Scatter / Gather I/O Sector alignment, Block Alignment Latching and a page: A read walk-through SQL Server I/O Sizes Data cache maintenance PAE and AWE Read Ahead User Mode and Kernel Mode (SYSTRAP) Sparse Files and Copy On Write (COW) Pages Locked Pages Scribbler(s) and Bit flips Page Protection and Constant Pages Checksum vs Torn Stale Read Stalled I/O WAL Protocol Write Ahead Logging ACID (Durability Property) Log records secured before data Hardened / Stable Media Log contains parity bit Commit Rollback Trigger Snapshot Synchronous vs Asynchronous I/O Sync: Wait for Completion Async: Post and Continue Overlapped Event Completion Port SQL Server 98% Async Usage Overlapped and HasOverlappedIoCompleted Network Layers Use Completion Port Backup/Restore Use Sync – Sequential Patterns dm_io_pending_io_requests Overlapped Structure Async Processing ~= CPU Package vs Phone Scatter / Gather I/O Consolidates or Distributes APIs ReadFileScatter WriteFileGather Increases Efficiency Used by SQL I/O Paths Used by Windows Page File Old Design: 6.x Sorting AWE Availability WriteMultiple # of 8K Pages Forward and Backward Buffer Pool Ramp-up Disk Memory Gather Scatter Sector AlignmentBlock Alignment Sector: Log Writes Block: Performance Avoid Crossovers DiskPart/DiskPar Utilities Discuss with your Vendor Double Touch Rewrites Defragment 4K Sectors Alignment: /kb/929491 To verify that an existing partition is aligned, divide the size of the stripe unit by the starting offset of the RAID disk group. Use the following syntax: ((Partition offset) * (Disk sector size)) / (Stripe unit size) Example of alignment calculations in bytes for a 256-KB stripe unit size: (63 * 512) / 262144 = 0.123046875(64 * 512) / 262144 = 0.125(128 * 512) /