深入解析Microsoft SQL Server 2008

需积分: 10 126 浏览量更新于2024-07-29 收藏 4.06MB PDF 举报

"Microsoft SQL Server 2008 Internals" 《Microsoft SQL Server 2008 Internals》是由Paul S. Randal、Kimberly L. Tripp、Conor Cunningham、Adam Machanic和Ben Nevarez等多位业界专家共同编著的一本深度解析Microsoft SQL Server 2008内核原理的专业书籍。这本书由微软技术院士David Campbell作序，并由Microsoft Press出版。本书主要探讨了SQL Server 2008的内部工作机制，包括但不限于以下几个关键知识点： 1. **数据库引擎**：深入解析SQL Server如何处理查询、事务管理和并发控制。这涵盖了查询优化器的工作原理、索引结构（如B-Trees和哈希索引）、锁和行版本控制机制。 2. **存储系统**：详细讲解数据页、分配单元、文件组、事务日志等存储结构，以及如何管理磁盘空间和内存。 3. **查询处理**：分析查询优化过程，包括查询计划的生成、统计信息的使用以及执行计划的优化策略。 4. **事务与恢复**：讨论SQL Server的事务特性，包括ACID属性的实现和崩溃恢复机制。 5. **并发控制**：解释了SQL Server如何通过锁定、多版本并发控制（MVCC）和行级并发来处理多个用户同时访问数据库。 6. **索引设计与优化**：涵盖各种类型的索引（如聚集索引、非聚集索引、覆盖索引等）及其对查询性能的影响，以及如何进行有效的索引设计。 7. **备份与恢复**：详细介绍了完整备份、差异备份、日志备份和差异日志备份的策略，以及如何利用这些策略进行数据恢复。 8. **故障诊断与调试**：提供了跟踪、日志阅读器、性能监视工具和其他诊断技术，帮助读者理解和解决SQL Server中的问题。 9. **性能调优**：讲解了性能监控、资源调度和性能计数器的使用，帮助管理员识别和解决性能瓶颈。 10. **服务架构**：描述了SQL Server服务的组件和交互，包括SQL Server代理、SQL Server Agent作业、复制服务等。这本书对于SQL Server的系统管理员、数据库开发人员和性能调优专家来说是一份宝贵的参考资料，它不仅揭示了SQL Server的内部工作机制，还提供了实用的技巧和最佳实践，帮助读者更高效地管理和优化数据库系统。书中丰富的实例和深入的技术解析使得复杂的概念易于理解，是提升SQL Server知识水平的必备读物。

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

yangyt

粉丝: 0
资源: 37

深入解析Microsoft SQL Server 2008

Microsoft SQL Server 2008 Internals (PDF 高清版)

Microsoft SQL Server 2008 Internals_中文

深入解析sqlserver 2008 Microsoft SQL Server 2008 Internals

SQL Server 2008 Internals

Professional SQL Server 2008 Internals

Microsoft SQL Server 2012 Internals

SQL Server 2008 Internals by Microsoft Press

Microsoft SQL Server 2012 Internals.pdf

Professional SQL Server 2012 Internals and Troubleshooting

Pro SQL Server Internals

最新资源