Delve inside the core SQL Server engine—and put that knowledge to work—with guidance from a team of well-known internals experts. Whether database developer, architect, or administrator, you’ll gain the deep knowledge you need to exploit key architectural changes—and capture the product’s full potential. Discover how SQL Server works behind the scenes, including:
What happens internally when SQL Server builds, expands, shrinks, and moves databases
How to use event tracking—from triggers to the Extended Events Engine
Why the right indexes can drastically reduce your query execution time
How to transcend normal row-size limits with new storage capabilities
How the Query Optimizer operates
Multiple techniques for troubleshooting problematic query plans
When to force SQL Server to reuse a cached query plan—or create a new one
What SQL Server checks internally when running DBCC
How to choose among five isolation levels and two concurrency models when working with multiple concurrent users
Chapter 1 SQL Server 2008 Architecture and Configuration
SQL Server Editions
SQL Server Metadata
Components of the SQL Server Engine
The SQLOS
The Scheduler
Memory
SQL Server Resource Governor
SQL Server 2008 Configuration
SQL Server System Configuration
SQL Server Configuration Settings
Final Words
Chapter 2 Change Tracking, Tracing, and Extended Events
The Basics: Triggers and Event Notifications
Change Tracking
Tracing and Profiling
Extended Events
Summary
Chapter 3 Databases and Database Files
System Databases
Sample Databases
Database Files
Creating a Database
Expanding or Shrinking a Database
Using Database Filegroups
Altering a Database
Databases Under the Hood
Setting Database Options
Database Snapshots
The tempdb Database
Database Security
Moving or Copying a Database
Compatibility Levels
Summary
Chapter 4 Logging and Recovery
Transaction Log Basics
Changes in Log Size
Backing Up and Restoring a Database
Summary
Chapter 5 Tables
Creating Tables
User-Defined Data Types
IDENTITY Property
Internal Storage
Constraints
Altering a Table
Heap Modification Internals
Summary
Chapter 6 Indexes: Internals and Management
Overview
Tools for Analyzing Indexes
Understanding Index Structures
Index Creation Options
Physical Index Structures
Special Index Structures
Data Modification Internals
Managing Index Structures
Summary
Chapter 7 Special Storage
Large Object Storage
Filestream Data
Sparse Columns
Data Compression
Table and Index Partitioning
Summary
Chapter 8 The Query Optimizer
Overview
What Is Optimization?
How the Query Optimizer Explores Query Plans
Optimizer Architecture
Statistics, Cardinality Estimation, and Costing
Index Selection
Partitioned Tables
Data Warehousing
Updates
Distributed Query
Extended Indexes
Plan Hinting
Summary
Chapter 9 Plan Caching and Recompilation
The Plan Cache
Caching Mechanisms
Plan Cache Internals
Objects in Plan Cache: The Big Picture
Multiple Plans in Cache
When to Use Stored Procedures and Other Caching Mechanisms
Troubleshooting Plan Cache Issues
Summary
Chapter 10 Transactions and Concurrency
Concurrency Models
Transaction Processing
Locking
Lock Compatibility
Internal Locking Architecture
Row-Level Locking vs. Page-Level Locking
Row Versioning
Controlling Locking
Summary
Chapter 11 DBCC Internals
Getting a Consistent View of the Database
Processing the Database Efficiently
Primitive System Catalog Consistency Checks
Allocation Consistency Checks
Per-Table Logical Consistency Checks
Cross-Table Consistency Checks
DBCC CHECKDB Output
DBCC CHECKDB Options
Database Repairs
Consistency-Checking Commands Other Than DBCC CHECKDB
Kalen Delaney is a Microsoft MVP who has worked with SQL Server since 1987. She's a subject-matter expert on Microsoft's official SQL Server courses and previously wrote Inside SQL Server (Microsoft Press). She lives in the Pacific Northwest.
Paul Randal and wife Kimberly L. Tripp are two of the most-sought-after SQL Server speakers in the world. A Microsoft MVP and Regional Director, Paul is also contributing editor to TechNet Magazine.
Kimberly L. Tripp is a SQL Server MVP and a Microsoft Regional Director and has worked with SQL Server since 1990. She lectures for a variety of events including Microsoft Tech*Ed and is married to co-editor and SQL guru Paul Randal.
Comments about Microsoft Press Microsoft® SQL Server® 2008 Internals:
This is one of the more comprehensive books available on how SQL Server actually works under the hood. The information is complete and helpful for administrators and developers.
The coverage includes architecture, internal state management, file storage and organization, logging, recovery, and how the database maintains the catalogs for all these structures. This is basically covered in the first 4 chapters.
For developers, the most helpful chapters will be 5-9. In these chapters, tables and indexes are covered very well with a whole chapter on the query optimizer. This explains why cached queries perform much better than queries passed without binding. The section on indexes breaks down when an index can help and when the data will not support a useful index.
Overall, a very good book for SQL Server admins and programmers. Definitely one of the best on the subject available.
