深入解析SQL Server 2008(英文影印版)（微软SQL Server开发团队必读之作）

.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!
table of contents
forewordxix
introductionxxi
1 sql server 2008 architecture and confi 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:
what do you think of this book? we want to hear from you!
viii table of contents
sql server resource governor 42
resource governor overview 42
resource governor controls 51
resource governor metadata 52
sql server 2008 confi guration 54
using sql server confi guration manager 54
confi guring network protocols 54
default network confi guration 55
managing services. 55
sql server system confi guration 57
operating system confi guration 57
trace flags 60
sql server confi guration settings 60
the default trace 71
final words 73
2 change tracking, tracing, and extended events 75
the basics: triggers and event notifi cations 75
run-time trigger behavior 76
change tracking 76
change tracking confi guration 77
change tracking run-time behavior 82
tracing and profi ling 86
sql trace architecture and terminology 86
security and permissions 88
getting started: profi 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
table of contents ix
sample databases 128
adventureworks 128
pubs 129
northwind 129
database files 130
creating a database. 132
a create database example 134
expanding or shrinking a database 135
automatic file expansion 135
manual file expansion 136
fast file initialization 136
automatic shrinkage. 136
manual shrinkage 137
using database filegroups 138
the default filegroup 139
a filegroup creation example 140
filestream filegroups 141
altering a database 142
alter database examples 143
databases under the hood 144
space allocation 145
setting database options 148
state options 151
cursor options 155
auto options 155
sql options 156
database recovery options 158
other database options. 159
database snapshots. 159
creating a database snapshot 160
space used by database snapshots 162
managing your snapshots 164
the tempdb database 164
objects in tempdb 165
optimizations in tempdb 166
best practices 168
tempdb space monitoring 169
database security 170
database access 170
managing database security 172
x table of contents
databases vs. schemas 173
principals and schemas 173
default schemas 174
moving or copying a database 175
detaching and reattaching a database 175
backing up and restoring a database. 177
moving system databases 177
moving the master database 179
compatibility levels. 179
summary 180
4 logging and recovery 181
transaction log basics 181
phases of recovery 184
reading the log 186
changes in log size 187
virtual log files 187
observing virtual log files 188
automatic truncation of virtual log files 192
maintaining a recoverable log. 193
automatic shrinking of the log 196
log file size 196
backing up and restoring a database 197
types of backups 197
recovery models 198
choosing a backup type. 203
restoring a database 203
summary 209
5 tables 211
creating tables 211
naming tables and columns 212
reserved keywords 213
delimited identifi ers 214
naming conventions. 215
data types. 215
much ado about null 241
table of contents xi
user-defi ned data types 244
identity property 245
internal storage 249
the sys.indexes catalog view 250
data storage metadata 251
data pages 254
examining data pages 256
the structure of data rows 260
finding a physical page 262
storage of fixed-length rows 265
storage of variable-length rows 267
storage of date and time data. 272
storage of sql_variant data 275
constraints 279
constraint names and catalog view information 280
constraint failures in transactions and multiple-row
data modifi cations 281
altering a table 282
changing a data type 283
adding a new column 284
adding, dropping, disabling, or enabling a constraint 284
dropping a column 285
enabling or disabling a trigger 286
internals of altering tables 286
heap modifi cation internals 289
allocation structures 289
inserting rows290
deleting rows. 291
updating rows294
summary 297
6 indexes: internals and management 299
overview 299
sql server index b-trees300
tools for analyzing indexes304
using the dm_db_index_physical_stats dmv304
using dbcc ind. 308
xii table of contents
understanding index structures 310
the dependency on the clustering key 311
nonclustered indexes 314
constraints and indexes 315
index creation options 316
ignore_dup_key 316
statistics_norecompute 317
maxdop 317
index placement 317
constraints and indexes 318
physical index structures 318
index row formats 318
clustered index structures 319
the non-leaf level(s) of a clustered index 320
analyzing a clustered index structure. 321
nonclustered index structures 326
special index structures 337
indexes on computed columns and indexed views 337
full-text indexes 345
spatial indexes346
xml indexes 346
data modifi cation internals 347
inserting rows 347
splitting pages348
deleting rows. 352
updating rows 358
table-level vs. index-level data modifi cation 362
logging 363
locking 363
fragmentation 363
managing index structures364
dropping indexes. 365
alter index 365
detecting fragmentation 368
removing fragmentation 369
rebuilding an index 371
summary 374
table of contents xiii
7 special storage 375
large object storage 375
restricted-length large object data (row-overfl ow data) 376
unrestricted-length large object data 380
storage of max-length data 386
filestream data 388
enabling filestream data for sql server 389
creating a filestream-enabled database 390
creating a table to hold filestream data 390
manipulating filestream data 392
metadata for filestream data 397
performance considerations for filestream data. 399
sparse columns400
management of sparse columns 400
column sets and sparse column manipulation .. 403
physical storage 405
metadata409
storage savings with sparse columns409
data compression 412
vardecimal 413
row compression 414
page compression 423
table and index partitioning 434
partition functions and partition schemes 434
metadata for partitioning 436
the sliding window benefi ts of partitioning 439
summary 442
8 the query optimizer 443
overview 443
tree format444
what is optimization? 445
how the query optimizer explores query plans446
rules446
properties 447
storage of alternatives—the “memo”449
operators 450
xiv table of contents
optimizer architecture 456
before optimization 456
simplifi cation 457
trivial plan/auto-parameterization. 457
limitations. 459
the memo—exploring multiple plans effi ciently. 459
statistics, cardinality estimation, and costing 462
statistics design 463
density/frequency information 466
filtered statistics 468
string statistics 469
cardinality estimation details 470
limitations. 474
costing. 475
index selection 477
filtered indexes 480
indexed views. 482
partitioned tables 486
partition-aligned index views 490
data warehousing 490
updates 491
halloween protection 494
split/sort/collapse 495
merge 497
wide update plans 499
sparse column updates 502
partitioned updates 502
locking 505
distributed query 507
extended indexes 510
full-text indexes 510
xml indexes 510
spatial indexes 510
plan hinting 511
debugging plan issues 513
{hash

.　　■ Chapter 11 DBCC Internals
　　A twelfth chapter covering the details of reading query plans is available in the companion content (which is described in the next section). This chapter, called “Query Execution,” was part of my previous book, Inside SQL Server 2005: Query Tuning and Optimization. Because 99 percent of the chapter is still valid for SQL Server 2008, we have included it “as is” for your additional reference.
　　Companion Content
　　This book features a companion Web site that makes available to you all the code used in the book, organized by chapter. The companion content also includes an extra chapter from my previous book, as well as the “History of SQL Server” chapter from my book SQL Server 2000. The site also provides extra scripts and tools to enhance your experience and understanding of SQL Server internals. As errors are found and reported, they will also be posted online. You can access this content from the companion site at this address:
　　http://www.SQLServerInternals.com/companion.
　　System Requirements
　　To use the code samples, you’ll need Internet access and a system capable of running SQL Server 2008 Enterprise or Developer edition. To get system requirements for SQL Server 2008 and to obtain a trial version, go to http://www.microsoft.com/downloads.
　　Introduction xxiii
　　Support for This Book
　　Every effort has been made to ensure the accuracy of this book and the contents of the companion Web site. As corrections or changes are collected, they will be added to a Microsoft Knowledge Base article.
　　Microsoft Press provides support for books at the following Web site:
　　http://www.microsoft.com/learning/support/books/Questions and Comments
　　If you have comments, questions, or ideas regarding the book, or questions that are not answered by visiting the sites above, please send them to Microsoft Press via e-mail to mspinput@microsoft.com
　　Or via postal mail to
　　Microsoft Press
　　Attn: Microsoft SQL Server 2008 Internals Editor
　　One Microsoft Way
　　Redmond, WA 98052-6399
　　Please note that Microsoft software product support is not offered through the above addresses. Acknowledgments
　　As always, a work like this is not an individual effort, and for this current volume, it is truer than ever. I was honored to have four other SQL Server experts join me in writing SQL Server 2008 Internals, and I truly could not have written this book alone. I am grateful to Adam Machanic, Paul Randal, Conor Cunningham, and Kimberly Tripp for helping to make this book a reality. In addition to my brilliant co-authors, this book could never have seen the light of day with help and encouragement from many other people. ..
　　First on my list is you, the readers. Thank you to all of you for reading what I have written. Thank you to those who have taken the time to write to me about what you thought of the book and what else you want to learn about SQL Server. I wish I could answer every question in detail. I appreciate all your input, even when I’m unable to send you a complete reply. One particular reader of one of my previous books, Inside SQL Server 2005: The Storage Engine, deserves particular thanks. I came to know Ben Nevarez as a very astute reader who found some uncaught errors and subtle inconsistencies and politely and succinctly reported them to me through my Web site. After a few dozen e-mails, I started to look forward to Ben’s e-mails and was delighted when I fi nally got the chance to meet him. Ben is now my most valued technical reviewer, and I am deeply indebted to him for his extremely careful reading of every one of the chapters.
　　xxiv Introduction
　　As usual, the SQL Server team at Microsoft has been awesome. Although Lubor Kollar and Sunil Agarwal were not directly involved in much of the research for this book, I always knew they were there in spirit, and both of them always had an encouraging word whenever I saw them. Boris Baryshnikov, Kevin Farlee, Marcel van der Holst, Peter Byrne, Sangeetha Shekar, Robin Dhamankar, Artem Oks, Srini Acharya, and Ryan Stonecipher met with me and responded to my (sometimes seemingly endless) e-mails. Jerome Halmans, Joanna Omel, Nikunj Koolar, Tres London, Mike Purtell, Lin Chan, and Dipti Sangani also offered valuable technical insights and information when responding to my e-mails. I hope they all know how much I appreciated every piece of information I received.
　　I am also indebted to Bob Ward, Bob Dorr, and Keith Elmore of the SQL Server Product Support team, not just for answering occasional questions but for making so much information about SQL Server available through white papers, conference presentations, and Knowledge Base articles. I am grateful to Alan Brewer and Gail Erickson for the great job they and their User Education team did putting together the SQL Server documentation in SQL Server Books Online.
　　And, of course, Buck Woody deserves my gratitude many times over. First from his job in the User Education group, then as a member of the SQL Server development team, he was always there when I had an unanswered question. His presentations and blog posts are always educational as well as entertaining, and his generosity and unfl agging good spirits are a true inspiration. I would also like to thank Leona Lowry and Cheryl Walter for fi nding me offi ce space in the same building as most of the SQL Server team. The welcome they gave me was much appreciated.
　　I would like to extend my heartfelt thanks to all of the SQL Server MVPs, but most especially Erland Sommarskog. Erland wrote the section in Chapter 5 on collations just because he thought it was needed, and that someone who has to deal with only the 26 letters of the English alphabet could never do it justice. Also deserving of special mention are Tibor Karaszi and Roy Harvey, for all the personal support and encouragement they gave me. Other MVPs who inspired me during the writing of this volume are Tony Rogerson, John Paul Cook, Steve Kass, Paul Nielsen, Hugo Kornelis, Tom Moreau, and Linchi Shea. Being a part of the SQL Server MVP team continues to be one of the greatest honors and privileges of my professional life. I am deeply indebted to my students in my “SQL Server Internals” classes, not only for their enthusiasm for the SQL Server product and for what I have to teach and share with them, but for all they have to share with me. Much of what I have learned has been inspired by questions from my curious students. Some of my students, such as Cindy Gross and Lara Rubbelke, have become friends (in addition to becoming Microsoft employees) and continue to provide ongoing inspiration.
　　Most important of all, my family continues to provide the rock-solid foundation I need to do the work that I do. My husband, Dan, continues to be the guiding light of my life after 24 years of marriage. My daughter, Melissa, and my three sons, Brendan, Rickey, and Connor, Introduction xxv
　　are now for the most part all grown, and are all generous, loving, and compassionate people. I feel truly blessed to have them in my life.
　　Kalen Delaney
　　Paul Randal
　　I’ve been itching to write a complete description of what DBCC CHECKDB does for many years now—not least to get it all out of my head and make room for something else! When Kalen asked me to write the “Consistency Checking” chapter for this book, I jumped at the chance, and for that my sincere thanks go to Kalen. I’d like to give special thanks to two people from Microsoft, among the many great folks I worked with there (and in many cases still do). The fi rst is Ryan Stonecipher, who I hired away from being an Escalation Engineer in SQL Product Support in late 2003 to work with me on DBCC, and who was suddenly thrust into complete ownership of 100,000+ lines of DBCC code when I become the team manager two months later. I couldn’t have asked for more capable hands to take over my precious DBCC. . . . The second is Bob Ward, who heads up the SQL Product Support team and has been a great friend since my early days at Microsoft. We must have collaborated on hundreds of cases of corruption over the years, and I’ve yet to meet someone with more drive for solving customer problems and improving Microsoft SQL Server. Thanks must also go to Steve Lindell, the author of the original online consistency checking code for SQL Server 2000, who spent many hours patiently explaining how it worked in 1999. Finally, I’d like to thank my wife, Kimberly, who is, along with Katelyn and Kiera, the other passions in my life apart from SQL Server.
　　Kimberly Tripp
　　First, I want to thank my good friend Kalen, for inviting me to participate in this title. After working together in various capacities—even having formed a company together back in 1996—it’s great to fi nally have our ideas and content together in a book as deep and technical as this. In terms of performance tuning, indexes are critical; there’s no better way to improve a system than by creating the right indexes. However, knowing what’s right takes multiple components, some of which is only known after experience, after testing, and after seeing something in action. For this, I want to thank many of you—readers, students, conference attendees, customers—those of you who have asked the questions, shown me interesting scenarios, and stayed late to “play” and/or just fi gure it out. It’s the deep desire to know why something is working the way that it is that keeps this product interesting to me and has always made me want to dive deeper and deeper into understanding what’s really going on. For that, I thank the SQL team in general—the folks that I’ve met and worked with over the years have been inspiring, intelligent, and insightful. Specifi cally, I want to thank a few folks on the SQL team who have patiently, quickly, and thoroughly responded to questions about what’s really going on and often, why: Conor Cunningham, xxvi Introduction
　　Cesar Galindo-Legaria, and from my early days with SQL Server, Dave Campbell, Nigel Ellis, and Rande Blackman. Gert E. R. Drapers requires special mention due to the many hours spent together over the years where we talked, argued, and fi gured it out. And, to Paul, my best friend and husband, who before that was also a good source of SQL information. We just don’t talk about it anymore . . . at home. OK, maybe a little.
　　Conor Cunningham
　　I’d like to thank Bob Beauchemin and Milind Joshi for their efforts to review my chapter, “The Query Optimizer,” in this book for technical correctness. I’d also like to thank Kimberly Tripp and Paul Randal for their encouragement and support while I wrote this chapter. Finally, I’d like to thank all the members of the SQL Server Query Processor team who answered many technical questions for me.
　　Adam Machanic
　　I would like to, fi rst and foremost, extend my thanks to Kalen Delaney for leading the effort of this book from conception through reality. Kalen did a great job of keeping us focused and on task, as well as helping to fi nd those hidden nuggets of information that make a book like this one great. A few Microsoft SQL Server team members dedicated their time to helping review my work: Jerome Halmans and Fabricio Voznika from the Extended Events team, and Mark Scurrell from the Change Tracking team. I would like to thank each of you for keeping me honest, answering my questions, and improving the quality of my chapter.
　　Finally, I would like to thank Kate and Aura, my wife and daughter, who always understand when I disappear into the offi ce for a day or two around deadline time. ...