深入解析SQL Server 2008

1　SQL Server 2008 Architecture and Configuration

SQL Server Editions

SQL Server Metadata

Compatibility Views

Catalog Views

Other Metadata

Components of the SQL Server Engine

Observing Engine Behavior

Protocols

The Relational Engine

The Storage Engine

The SQLOS

NUMA Architecture

The Scheduler

SQL Server Workers

Binding Schedulers to CPUs

The Dedicated Administrator Connection (DAC)

Memory

The Buffer Pool and the Data Cache

Access to In-Memory Data Pages

Managing Pages in the Data Cache

The Free Buffer List and the Lazywriter

Checkpoints

Managing Memory in Other Caches

Sizing Memory

Sizing the Buffer Pool

SQL Server Resource Governor

Resource Governor Overview

Resource Governor Controls

Resource Governor Metadata

SQL Server 2008 Configuration

Using SQL Server Configuration Manager

Configuring Network Protocols

Default Network Configuration

Managing Services

SQL Server System Configuration

Operating System Configuration

Trace Flags

SQL Server Configuration Settings

The Default Trace

Final Words

2　Change Tracking, Tracing, and Extended Events

The Basics: Triggers and Event Notifi cations

Run-Time Trigger Behavior

Change Tracking

Change Tracking Configuration

Change Tracking Run-Time Behavior

Tracing and Profiling

SQL Trace Architecture and Terminology

Security and Permissions

Getting Started: Profi ler

Server-Side Tracing and Collection

Extended Events

Components of the XE Infrastructure

Event Sessions

Extended Events DDL and Querying

Summary

3　Databases and Database Files

System Databases

master

model

tempdb

The Resource Database

msdb

Sample Databases

AdventureWorks

pubs

Northwind

Database Files

Creating a Database

A CREATE DATABASE Example

Expanding or Shrinking a Database

Automatic File Expansion

Manual File Expansion

Fast File Initialization

Automatic Shrinkage

Manual Shrinkage

Using Database Filegroups

The Default Filegroup

A FILEGROUP CREATION Example

Filestream Filegroups

Altering a Database

ALTER DATABASE Examples

Databases Under the Hood

Space Allocation

Setting Database Options

State Options

Cursor Options

Auto Options

SQL Options

Database Recovery Options

Other Database Options

Database Snapshots

Creating a Database Snapshot

Space Used by Database Snapshots

Managing Your Snapshots

The tempdb Database

Objects in tempdb

Optimizations in tempdb

Best Practices

tempdb Space Monitoring

Database Security

Database Access

Managing Database Security

Databases vs. Schemas

Principals and Schemas

Default Schemas

Moving or Copying a Database

Detaching and Reattaching a Database

Backing Up and Restoring a Database

Moving System Databases

Moving the master Database

Compatibility Levels

Summary

4　Logging and Recovery

Transaction Log Basics

Phases of Recovery

Reading the Log

Changes in Log Size

Virtual Log Files

Observing Virtual Log Files

Automatic Truncation of Virtual Log Files

Maintaining a Recoverable Log

Automatic Shrinking of the Log

Log File Size

Backing Up and Restoring a Database

Types of Backups

Recovery Models

Choosing a Backup Type

Restoring a Database

Summary

5　Tables

Creating Tables

Naming Tables and Columns

Reserved Keywords

Delimited Identifiers

Naming Conventions

Data Types

Much Ado About NULL

User-Defi ned Data Types

IDENTITY Property

Internal Storage

The sys.indexes Catalog View

Data Storage Metadata

Data Pages

Examining Data Pages

The Structure of Data Rows

Finding a Physical Page

Storage of Fixed-Length Rows

Storage of Variable-Length Rows

Storage of Date and Time Data

Storage of sql_variant Data

Constraints

Constraint Names and Catalog View Information

Constraint Failures in Transactions and Multiple-Row Data Modifi cations

Altering a Table

Changing a Data Type

Adding a New Column

Adding, Dropping, Disabling, or Enabling a Constraint

Dropping a Column

Enabling or Disabling a Trigger

Internals of Altering Tables

Heap Modifi cation Internals

Allocation Structures

Inserting Rows

Deleting Rows

Updating Rows

Summary

6　Indexes: Internals and Management

Overview

SQL Server Index B-trees

Tools for Analyzing Indexes

Using the dm_db_index_physical_stats DMV

Using DBCC IND

Understanding Index Structures

The Dependency on the Clustering Key

Nonclustered Indexes

Constraints and Indexes

Index Creation Options

IGNORE_DUP_KEY

STATISTICS_NORECOMPUTE

MAXDOP

Index Placement

Constraints and Indexes

Physical Index Structures

Index Row Formats

Clustered Index Structures

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

Analyzing a Clustered Index Structure

Nonclustered Index Structures

Special Index Structures

Indexes on Computed Columns and Indexed Views

Full-Text Indexes

Spatial Indexes

XML Indexes

Data Modifi cation Internals

Inserting Rows

Splitting Pages

Deleting Rows

Updating Rows

Table-Level vs　Index-Level Data Modifi cation

Logging

Locking

Fragmentation

Managing Index Structures

Dropping Indexes

ALTER INDEX

Detecting Fragmentation

Removing Fragmentation

Rebuilding an Index

Summary

7　Special Storage

Large Object Storage

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

Unrestricted-Length Large Object Data

Storage of MAX-Length Data

Filestream Data

Enabling Filestream Data for SQL Server

Creating a Filestream-Enabled Database

Creating a Table to Hold Filestream Data

Manipulating Filestream Data

Metadata for Filestream Data

Performance Considerations for Filestream Data

Sparse Columns

Management of Sparse Columns

Column Sets and Sparse Column Manipulation

Physical Storage

Metadata

Storage Savings with Sparse Columns

Data Compression

Vardecimal

Row Compression

Page Compression

Table and Index Partitioning

Partition Functions and Partition Schemes

Metadata for Partitioning

The Sliding Window Benefits of Partitioning

Summary

8　The Query Optimizer

Overview

Tree Format

What Is Optimization?

How the Query Optimizer Explores Query Plans

Rules

Properties

Storage of Alternatives—The “Memo”

Operators

Optimizer Architecture

Before Optimization

Simplifi cation

Trivial Plan/Auto-Parameterization

Limitations

The Memo—Exploring Multiple Plans Effi ciently

Statistics, Cardinality Estimation, and Costing

Statistics Design

Density/Frequency Information

Filtered Statistics

String Statistics

Cardinality Estimation Details

Limitations

Costing

Index Selection

Filtered Indexes

Indexed Views

Partitioned Tables

Partition-Aligned Index Views

Data Warehousing

Updates

Halloween Protection

Split/Sort/Collapse

Merge

Wide Update Plans

Sparse Column Updates

Partitioned Updates

Locking

Distributed Query

Extended Indexes

Full-Text Indexes

XML Indexes

Spatial Indexes

Plan Hinting

Debugging Plan Issues

{HASH | ORDER} GROUP

{MERGE | HASH | CONCAT } UNION

FORCE ORDER, {LOOP | MERGE | HASH } JOIN

INDEX=indexname | indexid

FORCESEEK

FAST number_rows

MAXDOP N

OPTIMIZE FOR

PARAMETERIZATION {SIMPLE | FORCED}

NOEXPAND

USE PLAN

Summary

9　Plan Caching and Recompilation

The Plan Cache

Plan Cache Metadata

Clearing Plan Cache

Caching Mechanisms

Adhoc Query Caching

Optimizing for Adhoc Workloads

Simple Parameterization

Prepared Queries

Compiled Objects

Causes of Recompilation

Plan Cache Internals

Cache Stores

Compiled Plans

Execution Contexts

Plan Cache Metadata

Handles

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_text_query_plan

sys.dm_exec_cached_plans

sys.dm_exec_cached_plan_dependent_objects

sys.dm_exec_requests

sys.dm_exec_query_stats

Cache Size Management

Costing of Cache Entries

Objects in Plan Cache: The Big Picture

Multiple Plans in Cache

When to Use Stored Procedures and Other Caching Mechanisms

Troubleshooting Plan Cache Issues

Wait Statistics Indicating Plan Cache Problems

Other Caching Issues

Handling Problems with Compilation and Recompilation

Plan Guides and Optimization Hints

Summary

10　Transactions and Concurrency

Concurrency Models

Pessimistic Concurrency

Optimistic Concurrency

Transaction Processing

ACID Properties

Transaction Dependencies

Isolation Levels

Locking

Locking Basics

Spinlocks

Lock Types for User Data

Lock Modes

Lock Granularity

Lock Duration

Lock Ownership

Viewing Locks

Locking Examples

Lock Compatibility

Internal Locking Architecture

Lock Partitioning

Lock Blocks

Lock Owner Blocks

syslockinfo Table

Row-Level Locking vs　Page-Level Locking

Lock Escalation

Deadlocks

Row Versioning

Overview of Row Versioning

Row Versioning Details

Snapshot-Based Isolation Levels

Choosing a Concurrency Model

Controlling Locking

Lock Hints

Summary

11　DBCC Internals

Getting a Consistent View of the Database

Obtaining a Consistent View

Processing the Database Effi ciently

Fact Generation

Using the Query Processor

Batches

Reading the Pages to Process

Parallelism

Primitive System Catalog Consistency Checks

Allocation Consistency Checks

Collecting Allocation Facts

Checking Allocation Facts

Per-Table Logical Consistency Checks

Metadata Consistency Checks

Page Audit

Data and Index Page Processing

Column Processing

Text Page Processing

Cross-Page Consistency Checks

Cross-Table Consistency Checks

Service Broker Consistency Checks

Cross-Catalog Consistency Checks

Indexed-View Consistency Checks

XML-Index Consistency Checks

Spatial-Index Consistency Checks

DBCC CHECKDB Output

Regular Output

SQL Server Error Log Output

Application Event Log Output

Progress Reporting Output

DBCC CHECKDB Options

NOINDEX

Repair Options

ALL_ERRORMSGS

EXTENDED_LOGICAL_CHECKS

NO_INFOMSGS

TABLOCK

ESTIMATEONLY

PHYSICAL_ONLY

DATA_PURITY

Database Repairs

Repair Mechanisms

Emergency Mode Repair

What Data Was Deleted by Repair?

Consistency-Checking Commands Other Than DBCC CHECKDB

DBCC CHECKALLOC

DBCC CHECKTABLE

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS

Summary

Index

《深入解析SQL Server 2008(英文版)》是讲述SQL Server关系数据库引擎内部机理和架构的权威指南。书中详细阐述了SQL Server处理查询、管理数据的相关内容，包括SQL Server架构和配置、跟踪/扩展事件、日志和恢复、索引、表格、查询优化、事务/并发以及DBCC。
　　《深入解析SQL Server 2008(英文版)》适合中高级数据库开发人员阅读。

由Kalen Delaney编写的微软SQL Server图书一直是同类图书中的佼佼者，是SQL Sewer开发人员、架构师和DBA的案头必备书。如今。这本新书纳入微软阵容空前的“深入解析”（Internals）系列，增加了5位SQL Server顶级专家，深入剖析了SQL Server 2008的底层机理及其对应用程序的影响，更具权威性。
　　微软SQL Server开发团队必读之作
　　六位SQL Server专家巨献
　　深入剖析SQL Server 2008技术内幕
　　让Jim Gray和David Campbell拍案叫绝的圣经级著作