1 SQL Server 2008 Architecture and Configuration 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
SQL Server Resource Governor 42
Resource Governor Overview 42
Resource Governor Controls 51
Resource Governor Metadata 52
SQL Server 2008 Configuration 54
Using SQL Server Configuration Manager 54
Configuring Network Protocols 54
Default Network Configuration 55
Managing Services 55
SQL Server System Configuration 57
Operating System Configuration 57
Trace Flags 60
SQL Server Configuration Settings 60
The Default Trace 71
Final Words 73
2 Change Tracking,Tracing,and Extended Events 75
The Basics:Triggers and Event Notifications 75
Run-Time Trigger Behavior 76
Change Tracking 76
Change Tracking Configuration 77
Change Tracking Run-Time Behavior 82
Tracing and Profiling 86
SQL Trace Architecture and Terminology 86
Security and Permissions 88
Gettina Started:Profiler 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
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
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 Identifiers 214
Naming Conventions 215
Data Types 215
Much Ado About NULL 241
User-Defined 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 Modifications 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 Modification Internals 289
Allocation Structures 289
Inserting Rows 290
Deleting Rows 291
Updating Rows 294
Summary 297
6 Indexes:Internals and Management 299
Overview 299
SQL Server Index B-trees 300
Tools for Analyzing Indexes 304
Using the dm_db_index_physical_stats DMV 304
Using DBCC IND 308
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 Indexes 346
XML Indexes 346
Data Modification Internals 347
Inserting Rows 347
Splitting Pages 348
Deleting Rows 352
Updating Rows 358
Table-Level vs. Index-Level Data Modification 362
Logging 363
Locking 363
Fragmentation 363
Managing Index Structures 364
Dropping Indexes 365
ALTER INDEX 365
Detecting Fragmentation 368
Removing Fragmentation 369
Rebuilding an Index 371
Summary 374
7 Special Storage 375
Large Object Storage 375
Restricted-Length Large Object Data(Row-Overflow 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 Columns 400
Management of Sparse Columns 400
Column Sets and Sparse Column Manipulation 403
Physical Storage 405
Metadata 409
Storage Savings with Sparse Columns 409
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 Benefits of Partitioning 439
Summary 442
8 The Query Optimizer 443
Overview 443
Tree Format 444
What Is Optimization? 445
How the Query Optimizer Explores Query Plans 446
Rules 446
Properties 447
Storage of Alternatives—The"Memo" 449
Operators 450
Optimizer Architecture 456
Before Optimization 456
Simplification 457
Trivial Plan/Auto-Parameterization 457
Limitations 459
The Memo—Exploring Multiple Plans Efficiently 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|ORDER}GROUP 514
{MERGE|HASH|CONCAT}UNION 515
FORCE ORDER,{LOOP|MERGE|HASH}JOIN 516
INDEX=<indexname>|<indexid> 516
FORCESEEK 517
FAST<number_rows> 517
MAXDOP<N> 518
OPTIMIZE FOR 518
PARAMETERIZATION{SIMPLE|FORCED} 520
NOEXPAND 521
USE PLAN 521
Summary 523
9 Plan Caching and Recompilation 525
The Plan Cache 525
Plan Cache Metadata 525
Clearing Plan Cache 526
Caching Mechanisms 527
Adhoc Query Caching 528
Optimizing for Adhoc Workloads 530
Simple Parameterization 533
Prepared Queries 538
Compiled Objects 540
Causes of Recompilation 543
Plan Cache Internals 553
Cache Stores 553
Compiled Plans 555
Execution Contexts 555
Plan Cache Metadata 556
Handles 556
sys.dm_exec_sql_text 557
sys.dm_exec_query_plan 558
sys.dm_exec_text_query_plan 558
sys.dm_exec_cached_plans 559
sys.dm_exec_cached_plan_dependent_objects 559
sys.dm_exec_requests 560
sys.dm_exec_query_stats 560
Cache Size Management 561
Costing of Cache Entries 564
Objects in Plan Cache:The Big Picture 565
Multiple Plans in Cache 567
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
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 Efficiently 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
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