The Guru's Guide to SQL Server Architecture and Internals

By Ken Henderson

Publisher : Addison Wesley
Pub Date : October 24, 2003
ISBN : 0-201-70047-6
Pages : 1072





"I can pretty much guarantee that anyone who uses SQL Server on a regular basis (even those located in Redmond working on SQL Server) can learn something new from reading this book."
-David Campbell, Product Unit Manager,Relational Server Team, Microsoft Corporation

The latest book from the highly regarded and best-selling author Ken Henderson, The Guru's Guide to SQL Server Architecture and Internals is the consummate reference to Microsoft SQL Server. Picking up where documentation and white papers leave off, this book takes an all-inclusive approach to provide the most depth and breadth of coverage of any book on SQL Server architecture, internals, and tuning.

Blending in-depth discussion with practical application, the guide begins with several chapters on the fundamental Windows technologies behind SQL Server, including processes and threads, memory management, Windows I/O, and networking. The focus then moves on to the architectural details of SQL Server and how to practically apply them.

The entire SQL Server product is covered--not just the functionality that resides within the core executable or product features that have been in place for years. SQL Server has matured and broadened substantially with each release, and the author explores the "fringe" technologies that have yet to be covered elsewhere, including Notification Services, Full Text Search, SQLXML, replication, DTS, and a host of others.

Throughout the book, the author uses WinDbg, Microsoft's free downloadable symbolic debugger, to look under the hood of SQL Server. Armed with new debugging and coding skills, readers will be ready to master SQL Server on their own.

The accompanying CD-ROM is packed with additional material, including full source code for the book's 900+ examples, as well as three invaluable tools: DTSDIAG, the VBODSOLE Library, and DTS Package Guru. DTSDIAG allows developers and administrators to simultaneously collect Profiler traces, perform logs, blocking script output, system event logs, and SQLDIAG reports from a specified SQL Server. The VBODSOLE Library features more than twenty new COM-based functions for Transact-SQL, including T-SQL enhancements such as array-manipulation routines, financial functions, string-manipulation functions, and system functions. DTS Package Guru is a .NET-based package editor for SQL Server's Data Transformation Services that allows editing of any modifiable package and supports the automation of mass package changes.

The Guru's Guide to SQL Server Architecture and Internals is the essential guide for database developers and administrators alike, regardless of skill level.


Copyright
List of Exercises
Foreword
Historical Perspective
Preface
Acknowledgments
Introduction
About Books Online
About WinDbg
About the Fundamentals
About the "How-To"
About the Breadth of Topics
About C++
About Visual C++ 6.0
About the Terms and Knowledge Measures
About SQL Server Versions
About Master Programming

About the Author
Part 1. Foundations
Chapter 1. Overview
Chapter Overview
Chapter Pairs
About the Code

Chapter 2. Windows Fundamentals
The Win32 API
User Mode vs. Kernel Mode
Processes and Threads
Virtual Memory vs. Physical Memory
Subsystems
Dynamic-Link Libraries
Tools
Recap
Knowledge Measure

Chapter 3. Processes and Threads
Processes
Threads
Thread Scheduling
Thread Synchronization

Chapter 4. Memory Fundamentals
Memory Basics
Virtual Memory
Heaps
Shared Memory

Chapter 5. I/O Fundamentals
I/O Basics
Asynchronous and Nonbuffered I/O
Scatter-Gather I/O
I/O Completion Ports
Memory-Mapped File I/O

Chapter 6. Networking Fundamentals
Overview
Named Pipes
Windows Sockets
Remote Procedure Call
Recap
Knowledge Measure

Chapter 7. COM
Overview
Before COM
The Dawn of COM
Basic Architecture
COM at Work
Threading Models
COM and SQL Server
Recap
Knowledge Measure

Chapter 8. XML
Overview
Simplicity Comes at a Price
A Brief History of XML
XML vs. HTML: An Example
Document Type Definitions
XML Schemas
Converting XML to HTML Using a Style Sheet
The Document Object Model
Processing XML with MSXML
Resources
Recap
Knowledge Measure


Part II. Subsystems, Components, and Technologies
Chapter 9. SQL Server as a Server
SQL Server and Networking
The SQL Server Executable
SQL Server's DLLs
SQL Server I/O
SQL Server Components
Recap
Knowledge Measure

Chapter 10. User Mode Scheduler
UMS Design Goals
User Mode vs. Kernel Mode Scheduling
Preemptive vs. Cooperative Tasking
How UMS Takes Over Scheduling
The UMS Scheduler
The UMS Scheduler Lists
Going Preemptive
Hidden Schedulers
DBCC SQLPERF(umsstats)
Recap
Knowledge Measure

Chapter 11. SQL Server Memory Management
Memory Regions
Sizing
The BPool
Primitive Allocations
AWE
The Lazywriter
The Memory Managers
Pulling It All Together
Recap
Knowledge Measure

Chapter 12. Query Processor
Key Terms and Concepts
Parsing
Optimization Stages
Optimization Limits
Parameter Sniffing
Auto-Parameterization
Indexing
Statistics
Indexable Expressions
Join Order and Type Selection
Logical and Physical Operators
Recap
Knowledge Measure

Chapter 13. Transactions
The ACID Test
How SQL Server Transactions Work
Types of Transactions
Avoiding Transactions Altogether
Automatic Transaction Management
Transaction Isolation Levels
Transaction Commands and Syntax
Debugging Transactions
Optimizing Transactional Code
Recap
Knowledge Measure

Chapter 14. Cursors
Overview
On Cursors and ISAM Databases
Types of Cursors
Appropriate Cursor Use
Transact-SQL Cursor Syntax
Configuring Cursors
Updating Cursors
Cursor Variables
Cursor Stored Procedures
Optimizing Cursor Performance
Recap
Knowledge Measure

Chapter 15. ODSOLE
Overview
The sp_OA Procedures
Automating with ODSOLE
Automating SQL-DMO by Using ODSOLE
Using ODSOLE to Automate Custom Objects
Recap
Knowledge Measure

Chapter 16. Full-Text Search
Overview
Architectural Details
Setting Up Full-Text Indexes
Full-Text Predicates
Rowset Functions
Recap
Knowledge Measure


Part III. Data Services
Chapter 17. Server Federations
Partitioned Views
Recap
Knowledge Measure

Chapter 18. SQLXML
Overview
MSXML
FOR XML
Using FOR XML
OPENXML
Using OPENXML
Accessing SQL Server over HTTP
URL Queries
Using URL Queries
Template Queries
Mapping Schemas
Updategrams
XML Bulk Load
Managed Classes
SQLXML Web Service (SOAP) Support
SQLXML Limitations
Recap
Knowledge Measure

Chapter 19. Notification Services
How It Works
Building Your Own Notification Application
Recap
Knowledge Measure

Chapter 20. Data Transformation Services
Overview
Packages
The Multiphase Data Pump
The Bulk Insert Task
The Data Driven Query Task
ActiveX Transformations
Other Types of Transformations
Lookup Queries
Workflow Properties
DTS and Transactions
Controlling Package Workflow through Scripting
Parameterized DTS Packages
The DSO Rowset Provider
Using DTS to Transform Replication Subscriptions
Custom Tasks
Controlling DTS through Automation
Recap
Knowledge Measure

Chapter 21. Snapshot Replication
Overview
The Snapshot Agent
Duties of the Snapshot and Distribution Agents
Updatable Subscriptions
Remote Agent Activation
Replication Cleanup
Recap
Knowledge Measure

Chapter 22. Transactional Replication
Overview
The MSrepl_commands Table
The sp_replcmds Procedure
The sp_repldone Procedure
Update Stored Procedures
Concurrent Snapshot Processing
Updatable Subscriptions
Validating Replicated Data
Skipping Errors
Cleanup
Recap
Knowledge Measure

Chapter 23. Merge Replication
Overview
Conflict Resolution
Generations
Filtering
Identity Range Management
Recap
Knowledge Measure


Part IV. Undocumented SQL Server
Chapter 24. Finding Undocumented Features
The syscomments Gold Mine
Goodies in sysobjects
Scripting Undocumented and System Objects
The Profiler Treasure Trove
Snooping around in the Installation Scripts
DLL Impots
Recap
Knowledge Measure

Chapter 25. DTSDIAG

Part V. Essays
Why I Really, Really Don't Like Fish!
Pseudo-Techie Tactics 101
How to Make Yourself Appear to Be an Expert via Newsgroup Postings
CD-ROM Warranty

Download
Another Database Books