Microsoft SQL Server 2008 内部技术深度解析

4星 · 超过85%的资源需积分: 10 83 浏览量更新于2024-07-29 收藏 4.08MB PDF 举报

《Microsoft SQL Server 2008 Internals》是一本由Kalen Delaney编著的专业级英文书籍，专为数据库管理员(DBA)量身打造。这本书深入探讨了Microsoft SQL Server 2008的核心特性和内部工作原理，为读者提供了全面理解这一数据库管理系统的技术细节和最佳实践。作者包括Paul S. Randal、Kimberly L. Tripp、Conor Cunningham、Adam Machanic和Ben Nevarez等业内专家，他们凭借丰富的经验和深厚的专业知识，将复杂的系统剖析为易于理解的概念。该书的封面由Microsoft Technical Fellow David Campbell撰写前言，强调了SQL Server 2008在当时的革新性，并预示了它在企业级应用中的关键作用。书中涵盖了众多主题，如查询优化、存储过程、事务管理、并发控制、内存管理、索引设计、数据库架构以及与Windows操作系统和其他Microsoft技术的集成。此外，还介绍了SQL Server 2008的新特性，比如透明数据加密(TDE)、全文搜索和扩展性方面的改进，这些都是DBA在维护和提升数据库性能时必须掌握的知识。版权方面，所有内容未经Microsoft Press许可不得复制或传播。该书已获得美国国会图书馆的登记号2008940524，并在美国印刷和装订。对于国际版本的获取信息，读者可通过联系当地的微软公司办公室或直接通过Microsoft Press International的传真（425）936-7329获取，或者访问微软出版社的官方网站www.microsoft.com/mspress，或发送反馈至mspinput@microsoft.com。《Microsoft SQL Server 2008 Internals》是一本极具价值的专业参考书，对于任何想要深入了解SQL Server 2008内部机制和技术细节的DBA来说，它是一本不可多得的宝典，可以帮助读者提升技能，优化数据库性能，应对不断变化的业务需求。

xvi Table of Contents

When to Use Stored Procedures and Other Caching Mechanisms . . . . . . . . . 568

Troubleshooting Plan Cache Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569

Wait Statistics Indicating Plan Cache Problems . . . . . . . . . . . . . . . . . . . . 569

Other Caching Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571

Handling Problems with Compilation and Recompilation . . . . . . . . . . . 572

Plan Guides and Optimization Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585

10 Transactions and Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . 587

Concurrency Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587

Pessimistic Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587

Optimistic Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588

Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588

ACID Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589

Transaction Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590

Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592

Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596

Locking Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596

Spinlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597

Lock Types for User Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597

Lock Modes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598

Lock Granularity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601

Lock Duration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .608

Lock Ownership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .609

Viewing Locks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .609

Locking Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612

Lock Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618

Internal Locking Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620

Lock Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622

Lock Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623

Lock Owner Blocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624

syslockinfo Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624

Row-Level Locking vs. Page-Level Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627

Lock Escalation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629

Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630

Row Versioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635

Overview of Row Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635

Row Versioning Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636

Snapshot-Based Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637

Choosing a Concurrency Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655

A05T626249.indd xvi 2/16/2009 3:22:16 PM

Table of Contents xvii

Controlling Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657

Lock Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661

11 DBCC Internals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663

Getting a Consistent View of the Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664

Obtaining a Consistent View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 665

Processing the Database Efﬁ ciently . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668

Fact Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668

Using the Query Processor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670

Batches. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 673

Reading the Pages to Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674

Parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675

Primitive System Catalog Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . 677

Allocation Consistency Checks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679

Collecting Allocation Facts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679

Checking Allocation Facts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 681

Per-Table Logical Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683

Metadata Consistency Checks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .684

Page Audit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685

Data and Index Page Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687

Column Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689

Text Page Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693

Cross-Page Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 694

Cross-Table Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705

Service Broker Consistency Checks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 706

Cross-Catalog Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 707

Indexed-View Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 707

XML-Index Consistency Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 708

Spatial-Index Consistency Checks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709

DBCC CHECKDB Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709

Regular Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710

SQL Server Error Log Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712

Application Event Log Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713

Progress Reporting Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 714

DBCC CHECKDB Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715

NOINDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715

Repair Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 716

ALL_ERRORMSGS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 716

EXTENDED_LOGICAL_CHECKS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717

A05T626249.indd xvii 2/16/2009 3:22:16 PM

xviii Table of Contents

NO_INFOMSGS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717

TABLOCK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717

ESTIMATEONLY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717

PHYSICAL_ONLY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 718

DATA_PURITY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 719

Database Repairs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 719

Repair Mechanisms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 720

Emergency Mode Repair . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721

What Data Was Deleted by Repair? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 722

Consistency-Checking Commands Other Than DBCC CHECKDB . . . . . . . . . . 723

DBCC CHECKALLOC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724

DBCC CHECKTABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725

DBCC CHECKFILEGROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725

DBCC CHECKCATALOG. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 726

DBCC CHECKIDENT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 726

DBCC CHECKCONSTRAINTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 727

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 727

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729

Microsoft is interested in hearing your feedback so we can continually improve our books and learning

resources for you. To participate in a brief online survey, please visit:

www.microsoft.com/learning/booksurvey/

What do you think of this book? We want to hear from you!

A05T626249.indd xviii 2/16/2009 3:22:16 PM

xix

Foreword

The developers who create products such as Microsoft SQL Server typically become experts

in one area of the technology, such as access methods or query execution. They live and

experience the product inside out and often know their component so deeply they acquire a

“curse of knowledge”: they possess so much detail about their particular domain, they ﬁ nd it

difﬁ cult to describe their work in a way that helps customers get the most out of the product.

Technical writers who create product-focused books, on the other hand, experience a

product outside in. Most of these authors acquire a broad, but somewhat shallow, surface

knowledge of the products they write about and produce valuable books, usually ﬁ lled with

many screenshots, which help new and intermediate users quickly learn how to get things

done with the product.

When the curse of knowledge meets surface knowledge, it leaves a gap where many of

the great capabilities created by product developers don’t get communicated in a way

that allows customers, particularly intermediate to advanced users, to use a product to

its full potential. This is where Microsoft SQL Server 2008 Internals comes in. This book,

like those in the earlier “Inside SQL Server” series, is the deﬁ nitive reference for how SQL

Server really works. Kalen Delaney has been working with the SQL Server product team for

over a decade, spending countless hours with developers breaking through the curse of

knowledge and then capturing the result in an incredibly clear form that allows intermediate

to advanced users to wring the most from the capabilities of SQL Server. In Microsoft SQL

Server 2008 Internals, Kalen is joined by four SQL Server experts who also share the gift

of breaking the curse. Conor Cunningham and Paul Randal have years of experience as

SQL Server product developers, and each of them is both a deep technical expert and a

gifted communicator. Kimberly Tripp and Adam Machanic both combine a passion to really

understand how things work and to then effectively share it with others. Kimberly and Adam

are both standing-room-only speakers at SQL Server events. This team has captured and

incorporated the details of key architectural changes for SQL Server 2008, resulting in a new,

comprehensive internals reference for SQL Server.

There is a litmus test you can use to determine if a technical product title deserves a

“ deﬁ nitive reference” classiﬁ cation. It’s a relatively easy test but a hard one for everybody to

conduct. The test, quite simply, is to look at how many of the developers who created the

product in question have a copy of the book on their shelves—and reference it. I can assure

you that each version of Inside Microsoft SQL Server that Kalen has produced has met this

test. Microsoft SQL Server 2008 Internals will, too.

Dave Campbell

Technical Fellow

Microsoft SQL Server

A06F626249.indd xix 2/11/2009 2:46:39 PM

剩余782页未读，继续阅读

orochi_gao

粉丝: 19
资源: 1

Microsoft SQL Server 2008 内部技术深度解析

SQL Server 2008 Internals by Microsoft Press

Microsoft SQL Server 2008 内部技术详解

深入探索Microsoft SQL Server 2008

深入解析sqlserver 2008 Microsoft SQL Server 2008 Internals

Microsoft SQL Server 2008 Internals_中文

Microsoft SQL Server 2008 Internals (PDF 高清版)

SQL Server 2008 Internals

Professional SQL Server 2008 Internals

Microsoft SQL Server 2012 Internals

Microsoft SQL Server 2012 Internals.pdf

最新资源