MicrosoftSQLServer2008Internals(PDF高清版)_MicrosoftSQLServer2008Internals资源-CSDN文库

5星 · 超过95%的资源需积分: 50 129 浏览量 2010-07-28 17:27:43 上传评论 1 收藏 35.56MB PDF 举报

资源推荐

资源详情

资源评论

Microsoft

Foreword by David Campbell

Microsoft Technical Fellow

SQL Server

2008 Internals

Paul S. Randal, Kimberly L. Tripp,

Conor Cunningham, Adam Machanic, and Ben Nevarez

Kalen Delaney

PUBLISHED BY

Microsoft Press

A Division of Microsoft Corporation

One Microsoft Way

Redmond, Washington 98052-6399

without the written permission of the publisher.

Library of Congress Control Number: 2008940524

Printed and bound in the United States of America.

1 2 3 4 5 6 7 8 9 QWT 4 3 2 1 0 9

Distributed in Canada by H.B. Fenn and Company Ltd.

A CIP catalogue record for this book is available from the British Library.

Microsoft Press books are available through booksellers and distributors worldwide. For further infor mation about

international editions, contact your local Microsoft Corporation ofﬁce or contact Microsoft Press International directly at

fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to mspinput@microsoft.com.

Microsoft, Microsoft Press, Access, Active Directory, Excel, MS, MSDN, Outlook, SQL Server, Visual SourceSafe, Win32,

Windows, and Windows Server are either registered trademarks or trademarks of the Microsoft group of companies. Other

product and company names mentioned herein may be the trademarks of their respective owners.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events

depicted herein are ﬁctitious. No association with any real company, organization, product, domain name, e-mail address,

logo, person, place, or event is intended or should be inferred.

This book expresses the author’s views and opinions. The information contained in this book is provided without any

express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will

be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.

Acquisitions Editor: Ken Jones

Developmental Editor: Sally Stickney

Project Editor: Lynn Finnel

Editorial Production: S4Carlisle Publishing Services

Technical Reviewer: Benjamin Nevarez; Technical Review services provided by Content Master, a member of CM Group, Ltd.

Cover: Tom Draper Design

Body Part No. X15-32079

vii

Table of Contents

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi

1 SQL Server 2008 Architecture and Conﬁ guration . . . . . . . . . . . . . 1

SQL Server Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

SQL Server Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Compatibility Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Catalog Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Other Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Components of the SQL Server Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Observing Engine Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Protocols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

The Relational Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

The Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

The SQLOS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

NUMA Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

The Scheduler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

SQL Server Workers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Binding Schedulers to CPUs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

The Dedicated Administrator Connection (DAC) . . . . . . . . . . . . . . . . . . . . 27

Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

The Buffer Pool and the Data Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Access to In-Memory Data Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Managing Pages in the Data Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

The Free Buffer List and the Lazywriter . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Checkpoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Managing Memory in Other Caches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Sizing Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Sizing the Buffer Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

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 vii 2/16/2009 3:22:16 PM

viii Table of Contents

SQL Server Resource Governor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Resource Governor Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Resource Governor Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Resource Governor Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

SQL Server 2008 Conﬁ guration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Using SQL Server Conﬁ guration Manager. . . . . . . . . . . . . . . . . . . . . . . . . . 54

Conﬁ guring Network Protocols. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Default Network Conﬁ guration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Managing Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

SQL Server System Conﬁ guration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Operating System Conﬁ guration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Trace Flags. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

SQL Server Conﬁ guration Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

The Default Trace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Final Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

2 Change Tracking, Tracing, and Extended Events . . . . . . . . . . . . . 75

The Basics: Triggers and Event Notiﬁ cations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

Run-Time Trigger Behavior. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Change Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Change Tracking Conﬁ guration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Change Tracking Run-Time Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

Tracing and Proﬁ ling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

SQL Trace Architecture and Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Security and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Getting Started: Proﬁ ler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Server-Side Tracing and Collection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

Extended Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Components of the XE Infrastructure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Event Sessions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Extended Events DDL and Querying . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

3 Databases and Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

System Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

master . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

tempdb. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

The Resource Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

msdb. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

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

剩余749页未读，继续阅读

评论收藏

内容反馈

zhb20046

2016-06-14

资源不错。很好的学习资料。
willliam2551

2011-09-14

页面质量还不错的，就是没有目录列表，看起来不大方便~
Switch135

2014-07-23

很好，边看边学英语。
shilin_fa

2013-12-18

很好，就是没有目录啊，找起来比较费劲
lxconan

2012-08-20

非常好，案头常备参考书。

前往

页

xuwedo2003

粉丝: 31
资源: 196

Microsoft SQL Server 2008 Internals (PDF 高清版)

最新资源

Microsoft SQL Server 2008 Internals (PDF 高清版)

Microsoft SQL Server 2008 Internals

SQL Server 2008 Internals

Microsoft.SQL.Server.2008.Internals

Microsoft SQL Server 2008 Internals_中文

Microsoft SQL Server 2012 Internals.pdf

Microsoft SQL Server 2012 Internals

MicrosoftSQLServer2012Internals.pdf 英文原版

深入解析SQL Server 2008中文版

Microsoft_R_SQL_Server_R_2008_Internals.pdf

深入解析sqlserver 2008 Microsoft SQL Server 2008 Internals

Professional SQL Server 2008 Internals

Professional SQL Server 2008 Internals and Troubleshooting

[SQL Server] Microsoft SQL Server 2012 技术内幕 (英文版)

Pro SQL Server 2008 Replication pdf

SQL Server 2012 管理高级教程（第2版）试读

Microsoft SQL Server 2008 - T-SQL Querying March 2009

SQL Server 2008中文版

深入解析SQL Server 2008（上）

深入解析SQL Server 2008

深入解析SQL Server 2008中文版.part1

Pro.SQL.Server.Internals

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

Professional Microsoft SQL Server 2012 Administration

Microsoft® SQL Server™ 2008 Integration Services unleashed

最新资源