深入解析SQL Server 2008 英文PDF电子书下载

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