深入解析Microsoft SQL Server 2008核心技术

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

《Microsoft SQL Server 2008 Internals》是由Microsoft Technical Fellow David Campbell撰写的一本专业书籍，深入探讨了Microsoft SQL Server 2008的核心内部结构和工作原理。该书由Microsoft Press出版，版权归属于Kalen Delaney，所有内容未经许可不得复制或传播。本书旨在帮助数据库管理员、开发人员和技术专业人士理解SQL Server 2008的各项内部机制，包括存储过程、查询优化、事务管理、内存管理、网络通信、安全模型和存储引擎等关键组件的工作方式。作者团队由Paul S. Randal、Kimberly L. Tripp、Conor Cunningham、Adam Machanic和Ben Nevarez组成，他们共同揭示了这款流行数据库系统的复杂性和高效性。书中涵盖了以下主要内容： 1. **系统架构**：介绍了SQL Server 2008的物理和逻辑结构，包括服务引擎、实例、数据库引擎、内存管理模块以及与操作系统和网络的交互。 2. **查询优化**：深入剖析查询计划的生成，涉及统计信息、索引设计、查询重写和执行计划的选择，以确保最佳性能。 3. **存储引擎**：详细讲解了关系数据的存储、访问和维护，包括B树索引、行级锁定和日志记录的实现。 4. **内存管理**：阐述了临时表空间、工作内存分配、缓存策略和内存池的使用，这对于内存敏感的应用至关重要。 5. **安全性**：讨论了身份验证、授权、加密和审计等方面，确保数据的安全和合规性。 6. **事务处理**：剖析了ACID特性、并发控制和恢复机制，以及如何处理分布式事务。 7. **故障恢复与备份**：介绍了SQL Server的备份策略、事务日志和备份还原过程，帮助读者理解和实施灾难恢复计划。 8. **性能调优**：提供了一些实用的性能分析工具和策略，以提升SQL Server的整体运行效率。此外，书中还提供了丰富的实例和案例研究，使读者能够将理论知识应用到实际场景中。对于那些希望深入了解SQL Server 2008底层特性和优化方法的专业人士来说，《Microsoft SQL Server 2008 Internals》是一本不可或缺的参考书。由于版权原因，这本书只能通过官方渠道购买或获取授权副本，且在全球范围内通过书店和分销商发行。国际版本的相关信息可通过Microsoft Press International获取。

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页未读，继续阅读

u010548280

粉丝: 2
资源: 15

深入解析Microsoft SQL Server 2008核心技术

Professional SQL Server 2008 Internals and Troubleshooting

Professional+SQL+Server+2012+Internals+and+Troubleshooting+

Microsoft SQL Server 2012 Internals

Microsoft SQL Server 2012 Internals.pdf

深入解析sqlserver 2008 Microsoft SQL Server 2008 Internals

Microsoft SQL Server 2008 Internals

Microsoft_R_SQL_Server_R_2008_Internals.pdf

SQL Server 2008 Internals

Microsoft SQL Server 2008 Internals_中文

Pro.SQL.Server.Always.On.Availability.Groups.1484220706.epub

最新资源