Introduction 1
Terms 1
Maintenance and Configuration 4
Pull-the-plug power outage testing 4
Atomic writes 6
Defragmenting disk drives 6
Backup hardening 8
4-KB disk sector sizes 9
Sector rewrite 9
Align with physical sector boundary 9
Align with 8 KB boundary 12
Larger transaction logs 13
Restore and attach 14
Format for 4 KB sectors run on smaller sectors 14
System and sample databases 15
Determining the formatted sector size of database 15
What sector sizes does SQL Server support? 15
Remote mirroring 15
Microsoft SQL Server 2005 I/O Error Message Changes and Additions 17
Error 823 17
Error 824 18
Error 832 20
Error 833 20
Microsoft SQL Server 2005 Enhancements 21
Checksum 21
Writes 22
Reads 22
Damage 22
PAGE_VERIFY usage 22
In-memory checksums 25
Latch enforcement 28
Checksum on backup and restore 29
Page-level restore 30
Database available during Undo phase 30
Torn page protection 30
Common reasons 31
Implementation 31
Stale read protection 32
Stalled I/O detection 33
sys.dm_io_pending_io_requests (DMV) 34
Read retry 35
Resource-based retries 36
Sort retries 36
Other read failure retries 36
Page audit 37
Log audit 38
Checkpoint 38
WriteMultiple extended 40
Read-ahead enhanced 41
Sparse files / Copy on write / Streams 41
Streams 41
Copy-on-write and sparse files 41
Stream and sparse file visibility 42
Snapshot reads 43
Instant file initialization 43
I/O affinity and snapshot backups 45
Locked memory pages 45
Idle server 46
Database mirroring (DBM) 48
Multiple instance access to read-only databases 48
Ramp up of local cache 49
Encrypted file systems (EFS) 49
DiskPar.exe 49
Always On high-availability data storage 50
SQLIOSim 50
Conclusion 50
References 51