Microsoft sql server I/o basics: Chapter 2

Microsoft SQL Server I/O Basics: Chapter 2

Microsoft SQL Server I/O Basics

Chapter 2


SQL Server 2000 SP4 and SQL Server 2005

Author: Bob Dorr (SQL Server Senior Escalation Engineer)

Reviewers: Mike Zwilling (SQL Server Development Manager)
Paul Randal (SQL Server Lead Program Manager)
Mirek Sztajno (SQL Server Program Manager)
Steve Schmidt (SQL Server Software Development Engineer)
Peter Byrne (SQL Server Software Development Engineer)
Eric Christensen (SQL Server Software Development Engineer)
Ryan Stonecipher (SQL Server Software Development Engineer)
George Reynya (SQL Server Software Development Engineer)
Kevin Farlee (SQL Server Program Manager)
Burzin Patel (SQL Server Program Manager)
Wei Xiao (SQL Server Technical Lead)
Kathy Lu (SQL Server Engine Test)
Bob Ward (SQL Server Senior Escalation Engineer)
Suresh Kandoth (SQL Server Escalation Engineer)
Published: June 2006

SUMMARY: Outlines the I/O features and enhancements in SQL Server 2000 Service Pack 4 and SQL Server 2005.

Copyright

The information that is contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2006 Microsoft Corporation. All rights reserved.

Microsoft, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Contents

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

Introduction

Microsoft® SQL Server™ 2005 continues to innovate and extend I/O performance and reliability. With new innovations come new terms, designs, and algorithms.

The SQL Server 2000 I/O Basics document is a prerequisite to the information contained in this document. Read it before you read this paper.

As the administrator of a SQL Server 2005 installation, you will find that visibility into the SQL Server I/O subsystem has been significantly increased. A primary focus of the SQL Server 2005 I/O design was overall stability of performance and data integrity as these relate to the outside influences of the I/O subsystem/path. The new and extended features provide both more visibility and more capabilities. You can maintain a SQL Server 2005 installation with high confidence in the system.

This white paper introduces new terms, discusses maintenance and configuration issues, new and improved error messages, and I/O enhancements.

After reading this document you will better understand SQL Server I/O needs and capabilities.