深入解析SQL Server 2008 英文PDF|Epub|txt|kindle电子书版本网盘下载

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

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

1 SQL Server 2008 Architecture and Configuration1

SQL Server Editions1

SQL Server Metadata2

Compatibility Views3

Catalog Views4

Other Metadata6

Components of the SQL Server Engine8

Observing Engine Behavior9

Protocols11

The Relational Engine12

The Storage Engine14

The SQLOS18

NUMA Architecture19

The Scheduler20

SQL Server Workers21

Binding Schedulers to CPUs24

The Dedicated Administrator Connection(DAC)27

Memory29

The Buffer Pool and the Data Cache29

Access to In-Memory Data Pages30

Managing Pages in the Data Cache30

The Free Buffer List and the Lazywriter31

Checkpoints32

Managing Memory in Other Caches34

Sizing Memory35

Sizing the Buffer Pool36

SQL Server Resource Governor42

Resource Governor Overview42

Resource Governor Controls51

Resource Governor Metadata52

SQL Server 2008 Configuration54

Using SQL Server Configuration Manager54

Configuring Network Protocols54

Default Network Configuration55

Managing Services55

SQL Server System Configuration57

Operating System Configuration57

Trace Flags60

SQL Server Configuration Settings60

The Default Trace71

Final Words73

2 Change Tracking,Tracing,and Extended Events75

The Basics:Triggers and Event Notifications75

Run-Time Trigger Behavior76

Change Tracking76

Change Tracking Configuration77

Change Tracking Run-Time Behavior82

Tracing and Profiling86

SQL Trace Architecture and Terminology86

Security and Permissions88

Gettina Started:Profiler89

Server-Side Tracing and Collection97

Extended Events108

Components of the XE Infrastructure108

Event Sessions118

Extended Events DDL and Querying121

Summary124

3 Databases and Database Files125

System Databases126

master126

model126

tempdb126

The Resource Database127

msdb128

Sample Databases128

AdventureWorks128

pubs129

Northwind129

Database Files130

Creating a Database132

A CREATE DATABASE Example134

Expanding or Shrinking a Database135

Automatic File Expansion135

Manual File Expansion136

Fast File Initialization136

Automatic Shrinkage136

Manual Shrinkage137

Using Database Filegroups138

The Default Filegroup139

A FILEGROUP CREATION Example140

Filestream Filegroups141

Altering a Database142

ALTER DATABASE Examples143

Databases Under the Hood144

Space Allocation145

Setting Database Options148

State Options151

Cursor Options155

Auto Options155

SQL Options156

Database Recovery Options158

Other Database Options159

Database Snapshots159

Creating a Database Snapshot160

Space Used by Database Snapshots162

Managing Your Snapshots164

The tempdb Database164

Objects in tempdb165

Optimizations in tempdb166

Best Practices168

tempdb Space Monitoring169

Database Security170

Database Access170

Managing Database Security172

Databases vs.Schemas173

Principals and Schemas173

Default Schemas174

Moving or Copying a Database175

Detaching and Reattaching a Database175

Backing Up and Restoring a Database177

Moving System Databases177

Moving the master Database179

Compatibility Levels179

Summary180

4 Logging and Recovery181

Transaction Log Basics181

Phases of Recovery184

Reading the Log186

Changes in Log Size187

Virtual Log Files187

Observing Virtual Log Files188

Automatic Truncation of Virtual Log Files192

Maintaining a Recoverable Log193

Automatic Shrinking of the Log196

Log File Size196

Backing Up and Restoring a Database197

Types of Backups197

Recovery Models198

Choosing a Backup Type203

Restoring a Database203

Summary209

5 Tables211

Creating Tables211

Naming Tables and Columns212

Reserved Keywords213

Delimited Identifiers214

Naming Conventions215

Data Types215

Much Ado About NULL241

User-Defined Data Types244

IDENTITY Property245

Internal Storage249

The sys.indexes Catalog View250

Data Storage Metadata251

Data Pages254

Examining Data Pages256

The Structure of Data Rows260

Finding a Physical Page262

Storage of Fixed-Length Rows265

Storage of Variable-Length Rows267

Storage of Date and Time Data272

Storage of sql_variant Data275

Constraints279

Constraint Names and Catalog View Information280

Constraint Failures in Transactions and Multiple-Row Data Modifications281

Altering a Table282

Changing a Data Type283

Adding a New Column284

Adding,Dropping,Disabling,or Enabling a Constraint284

Dropping a Column285

Enabling or Disabling a Trigger286

Internals of Altering Tables286

Heap Modification Internals289

Allocation Structures289

Inserting Rows290

Deleting Rows291

Updating Rows294

Summary297

6 Indexes:Internals and Management299

Overview299

SQL Server Index B-trees300

Tools for Analyzing Indexes304

Using the dm_db_index_physical_stats DMV304

Using DBCC IND308

Understanding Index Structures310

The Dependency on the Clustering Key311

Nonclustered Indexes314

Constraints and Indexes315

Index Creation Options316

IGNORE_DUP_KEY316

STATISTICS_NORECOMPUTE317

MAXDOP317

Index Placement317

Constraints and Indexes318

Physical Index Structures318

Index Row Formats318

Clustered Index Structures319

The Non-Leaf Level(s) of a Clustered Index320

Analyzing a Clustered Index Structure321

Nonclustered Index Structures326

Special Index Structures337

Indexes on Computed Columns and Indexed Views337

Full-Text Indexes345

Spatial Indexes346

XML Indexes346

Data Modification Internals347

Inserting Rows347

Splitting Pages348

Deleting Rows352

Updating Rows358

Table-Level vs. Index-Level Data Modification362

Logging363

Locking363

Fragmentation363

Managing Index Structures364

Dropping Indexes365

ALTER INDEX365

Detecting Fragmentation368

Removing Fragmentation369

Rebuilding an Index371

Summary374

7 Special Storage375

Large Object Storage375

Restricted-Length Large Object Data(Row-Overflow Data)376

Unrestricted-Length Large Object Data380

Storage of MAX-Length Data386

Filestream Data388

Enabling Filestream Data for SQL Server389

Creating a Filestream-Enabled Database390

Creating a Table to Hold Filestream Data390

Manipulating Filestream Data392

Metadata for Filestream Data397

Performance Considerations for Filestream Data399

Sparse Columns400

Management of Sparse Columns400

Column Sets and Sparse Column Manipulation403

Physical Storage405

Metadata409

Storage Savings with Sparse Columns409

Data Compression412

Vardecimal413

Row Compression414

Page Compression423

Table and Index Partitioning434

Partition Functions and Partition Schemes434

Metadata for Partitioning436

The Sliding Window Benefits of Partitioning439

Summary442

8 The Query Optimizer443

Overview443

Tree Format444

What Is Optimization?445

How the Query Optimizer Explores Query Plans446

Rules446

Properties447

Storage of Alternatives—The"Memo"449

Operators450

Optimizer Architecture456

Before Optimization456

Simplification457

Trivial Plan/Auto-Parameterization457

Limitations459

The Memo—Exploring Multiple Plans Efficiently459

Statistics,Cardinality Estimation,and Costing462

Statistics Design463

Density/Frequency Information466

Filtered Statistics468

String Statistics469

Cardinality Estimation Details470

Limitations474

Costing475

Index Selection477

Filtered Indexes480

Indexed Views482

Partitioned Tables486

Partition-Aligned Index Views490

Data Warehousing490

Updates491

Halloween Protection494

Split/Sort/Collapse495

Merge497

Wide Update Plans499

Sparse Column Updates502

Partitioned Updates502

Locking505

Distributed Query507

Extended Indexes510

Full-Text Indexes510

XML Indexes510

Spatial Indexes510

Plan Hinting511

Debugging Plan Issues513

{HASH|ORDER}GROUP514

{MERGE|HASH|CONCAT}UNION515

FORCE ORDER,{LOOP|MERGE|HASH}JOIN516

INDEX=<indexname>|<indexid>516

FORCESEEK517

FAST<number_rows>517

MAXDOP<N>518

OPTIMIZE FOR518

PARAMETERIZATION{SIMPLE|FORCED}520

NOEXPAND521

USE PLAN521

Summary523

9 Plan Caching and Recompilation525

The Plan Cache525

Plan Cache Metadata525

Clearing Plan Cache526

Caching Mechanisms527

Adhoc Query Caching528

Optimizing for Adhoc Workloads530

Simple Parameterization533

Prepared Queries538

Compiled Objects540

Causes of Recompilation543

Plan Cache Internals553

Cache Stores553

Compiled Plans555

Execution Contexts555

Plan Cache Metadata556

Handles556

sys.dm_exec_sql_text557

sys.dm_exec_query_plan558

sys.dm_exec_text_query_plan558

sys.dm_exec_cached_plans559

sys.dm_exec_cached_plan_dependent_objects559

sys.dm_exec_requests560

sys.dm_exec_query_stats560

Cache Size Management561

Costing of Cache Entries564

Objects in Plan Cache:The Big Picture565

Multiple Plans in Cache567

When to Use Stored Procedures and Other Caching Mechanisms568

Troubleshooting Plan Cache Issues569

Wait Statistics Indicating Plan Cache Problems569

Other Caching Issues571

Handling Problems with Compilation and Recompilation572

Plan Guides and Optimization Hints573

Summary585

10 Transactions and Concurrency587

Concurrency Models587

Pessimistic Concurrency587

Optimistic Concurrency588

Transaction Processing588

ACID Properties589

Transaction Dependencies590

Isolation Levels592

Locking596

Locking Basics596

Spinlocks597

Lock Types for User Data597

Lock Modes598

Lock Granularity601

Lock Duration608

Lock Ownership609

Viewing Locks609

Locking Examples612

Lock Compatibility618

Internal Locking Architecture620

Lock Partitioning622

Lock Blocks623

Lock Owner Blocks624

syslockinfo Table624

Row-Level Locking vs.Page-Level Locking627

Lock Escalation629

Deadlocks630

Row Versioning635

Overview of Row Versioning635

Row Versioning Details636

Snapshot-Based Isolation Levels637

Choosing a Concurrency Model655

Controlling Locking657

Lock Hints657

Summary661

11 DBCC Internals663

Getting a Consistent View of the Database664

Obtaining a Consistent View665

Processing the Database Efficiently668

Fact Generation668

Using the Query Processor670

Batches673

Reading the Pages to Process674

Parallelism675

Primitive System Catalog Consistency Checks677

Allocation Consistency Checks679

Collecting Allocation Facts679

Checking Allocation Facts681

Per-Table Logical Consistency Checks683

Metadata Consistency Checks684

Page Audit685

Data and Index Page Processing687

Column Processing689

Text Page Processing693

Cross-Page Consistency Checks694

Cross-Table Consistency Checks705

Service Broker Consistency Checks706

Cross-Catalog Consistency Checks707

Indexed-View Consistency Checks707

XML-Index Consistency Checks708

Spatial-Index Consistency Checks709

DBCC CHECKDB Output709

Regular Output710

SQL Server Error Log Output712

Application Event Log Output713

Progress Reporting Output714

DBCC CHECKDB Options715

NOINDEX715

Repair Options716

ALL_ERRORMSGS716

EXTENDED_LOGICAL_CHECKS717

NO_INFOMSGS717

TABLOCK717

ESTIMATEONLY717

PHYSICAL_ONLY718

DATA_PURITY719

Database Repairs719

Repair Mechanisms720

Emergency Mode Repair721

What Data Was Deleted by Repair?722

Consistency-Checking Commands Other Than DBCC CHECKDB723

DBCC CHECKALLOC724

DBCC CHECKTABLE725

DBCC CHECKFILEGROUP725

DBCC CHECKCATALOG726

DBCC CHECKIDENT726

DBCC CHECKCONSTRAINTS727

Summary727

Index729