SQL Server 2008 内部机制揭秘

sql2008

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

"SQL Server 2008 内部机制详解" 《Microsoft SQL Server 2008 Internals》是由Paul S. Randal、Kimberly L. Tripp、Conor Cunningham、Adam Machanic和Ben Nevarez等多位SQL Server领域的专家共同编著的一本深度解析SQL Server 2008内部工作原理的专业书籍。这本书由Microsoft Press出版，旨在为IT专业人士提供深入理解SQL Server 2008架构和内部操作的关键洞察。本书涵盖了SQL Server 2008的多个核心主题，包括但不限于： 1. **存储引擎**：书中详细介绍了SQL Server 2008的数据存储方式，如页和记录的组织结构，事务日志的工作原理，以及索引的构建和优化。读者将了解到如何更有效地查询和管理数据，以及如何优化存储性能。 2. **查询处理**：深入探讨了查询优化器的工作流程，包括查询计划的生成、统计信息的使用以及并行查询的处理。这些知识对于理解SQL Server如何执行查询和提升查询性能至关重要。 3. **锁和并发控制**：书中详细阐述了SQL Server 2008的并发控制机制，包括事务隔离级别、锁、快照隔离等，帮助读者理解和解决并发问题。 4. **内存管理**：讨论了SQL Server如何分配和管理内存，包括缓冲池、计划缓存和内存优化表等，这对于优化服务器性能和避免内存争用具有重要意义。 5. **复制与高可用性**：介绍了SQL Server 2008的复制技术，如事务复制、合并复制和快照复制，以及故障转移群集和数据库镜像等高可用性解决方案。 6. **服务架构**：解析了SQL Server服务的内部运作，包括SQL Server代理、服务 Broker 和其他后台进程，帮助读者了解这些服务如何协同工作以确保系统的稳定运行。 7. **故障诊断和调试**：提供了大量实用的故障排查技巧和工具，如DMV（动态管理视图）、XEvent（扩展事件）和SQL Profiler等，帮助DBA定位和解决问题。 8. **性能监控和调优**：涵盖了性能计数器、性能日志和警告，以及如何利用工具进行性能分析和调优，为日常的系统维护和性能提升提供指导。通过阅读《Microsoft SQL Server 2008 Internals》，读者不仅可以深化对SQL Server 2008内部机制的理解，还能掌握高级的故障诊断、性能优化和系统管理技能，这对于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页未读，继续阅读

Michael_Peng2013

粉丝: 0
资源: 1

SQL Server 2008 内部机制揭秘

windows internal 第六版 第一部分

教师节主题班会.pptx

学生网络安全教育主题班会.pptx

世界环境日主题班会.pptx

GNSS 经纬度 所有国家的电子围栏

JEEWEB Mybatis版本是一款基于SpringMVC+Spring+Mybatis+Mybatis Plus的JAVA WEB敏捷开发系统.zip

20190313-092954-旋转磁体产生的场对原子钟频率的影响

java毕设项目之基于springboot + vue 物流系统(源码+说明文档+mysql).zip

matlab的人体异常行为检测识别系统（源码，论文，GUI）.zip

java毕设项目之基于Spring Boot的中药材管理系统(源码+说明文档+mysql).zip

最新资源

windows internal 第六版第一部分

GNSS 经纬度所有国家的电子围栏