深入解析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 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
Kalen Delanay世界知名的SQL Server专家。微软SQLSewer MVP。从1 987年供职Sybase时与微软合作开发最早的SQL Server版本算起。她的SQL Sewer研发经验已达20多年。她本人以对SQL Server底层技术的精湛造诣享誉业内。所著Inside Microsoft SQL Server系列（本书前身）长期以来被奉为圣经级著作。
Paul S．Randal和KJmberly L．Tripp夫妇世界知名的SQL Server专家。微软SQL Sewer MVP。他们都曾长期效力于微软SQL Sewer开发团队。Randal更是在SQL Sewer多个版本中负责存储引擎的开发。
Conor Cunningham目前担任SQL Sewerver引擎主架构师。负责下一代引擎的设计和开发。Adam Machanic世界知名的SQL Server专家。微软SQL Server MVP。著名社区SQLblog．com创始人。名著《SQL Sewer 2005编程艺术》的作者。
Ben Nevarez资深D8A。有多年SQL Sewer管理经验。
Paul S．Randal和KJmberly L．Tripp夫妇世界知名的SQL Server专家。微软SQL Sewer MVP。他们都曾长期效力于微软SQL Sewer开发团队。Randal更是在SQL Sewer多个版本中负责存储引擎的开发。
Conor Cunningham目前担任SQL Sewerver引擎主架构师。负责下一代引擎的设计和开发。Adam Machanic世界知名的SQL Server专家。微软SQL Server MVP。著名社区SQLblog．com创始人。名著《SQL Sewer 2005编程艺术》的作者。
Ben Nevarez资深D8A。有多年SQL Sewer管理经验。
《深入解析SQL Server 2008(英文版)》是讲述SQL Server关系数据库引擎内部机理和架构的权威指南。书中详细阐述了SQL Server处理查询、管理数据的相关内容，包括SQL Server架构和配置、跟踪/扩展事件、日志和恢复、索引、表格、查询优化、事务/并发以及DBCC。
《深入解析SQL Server 2008(英文版)》适合中高级数据库开发人员阅读。
《深入解析SQL Server 2008(英文版)》适合中高级数据库开发人员阅读。
比价列表
公众号、微信群
缺书网微信公众号
扫码进群实时获取购书优惠