Table of Contents
Foreword
Acknowledgments
Introduction
A History of Inside Microsoft SQL Server
Series Structure
Inside Microsoft SQL Server 2005: T-SQL Querying
Inside Microsoft SQL Server 2005: T-SQL Programming
Inside Microsoft SQL Server 2005: The Storage Engine
Inside Microsoft SQL Server: Query Tuning and Optimization
Examples and Scripts
Topics Not Covered
Caveats and Disclaimers
How to Get Support
Companion Web Site
Microsoft Learning
1: A Performance Troubleshooting Methodology
Factors That Impact Performance
Application Architecture
Application Design
Transactions and Isolation Levels
Transact-SQL Code
Hardware Resources
SQL Server Configuration
Troubleshooting Overview
Creating a Baseline for Your Workload
Monitoring the Workload
Detecting, Isolating, and Troubleshooting Common Performance Problems
Summary
2: Tracing and Profiling
SQL Trace Architecture and Terminology
Internal Trace Components
Trace I/O Providers
Security and Permissions
ALTER TRACE Permission
Protecting Sensitive Event Data
Getting Started: Profiler
Profiler Basics
Saving and Replaying Traces
Server-Side Tracing and Collection
Scripting Server-Side Traces
Querying Server-Side Trace Metadata
Retrieving Data from Server-Side Traces
Stopping and Closing Traces
Investigating the Rowset Provider
Troubleshooting and Analysis with Traces
Commonly Used SQL Trace Event Classes
Performance Tuning
Identifying Exceptions
Debugging Deadlocks
Stored Procedure Debugging
Tracing Considerations and Design
The SQL Server Profiler Question
Reducing Trace Overhead
Max File Size, Rollover, and Data Collection
Auditing: SQL Server’s Built-in Traces
Default Trace
Blackbox Traces
C2 and Common Criteria Auditing
Summary
3: Query Execution
Query Processing and Execution Overview
Iterators
Properties of Iterators
Reading Query Plans
Query Plan Options
Analyzing Plans
Scans and Seeks
Seekable Predicates and Covered Columns
Bookmark Lookup
Joins
Aggregations
Unions
Advanced Index Operations
Subqueries
Parallelism
Inserts, Updates, and Deletes
Summary
4: Troubleshooting Query Performance
Compilation and Optimization
Compilation
Optimization
How the Query Optimizer Works
Detecting Problems in Plans
Cardinality Estimation Errors
Miscellaneous Warning Signs
Monitoring Query Performance
STATISTICS TIME
Query Improvements
Rewriting Your Query
Schema Improvements
Statistics Management
Creating Useful Indexes
Optimization Hints in SQL Server 2005
Query Processing Best Practices
Use Set-Oriented Programming
Provide the Optimizer with Constraints and Statistics Information
Avoid Unnecessary Complexity
Be Careful with Dynamic SQL
Summary
5: Plan Caching and Recompilation
The Plan Cache
Plan Cache Metadata
Clearing Plan Cache
Caching Mechanisms
Adhoc Query Caching
Autoparameterization
Prepared Queries
Compiled Objects
Causes of Recompilation
Plan Cache Internals
Cache Stores
Compiled Plans
Execution Plans
Plan Cache Metadata
Handles
sys.dm_exec_sql_text
sys.dm_exec_cached_plans
sys.dm_exec_cached_plan_dependent_objects.
sys.dm_exec_requests
sys.dm_exec_query_stats.
Cache Size Management
Local Memory Pressure
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
Troubleshooting Caching and Recompilation Summary
Plan Guides and Optimization Hints
Summary
6: Concurrency Problems
New Tools for Troubleshooting Concurrency
New Blocking Detection Tool: sys.dm_os_waiting_tasks
New Blocking Resolution Tool: Row-Versioning–Based Isolation Levels
Types of Concurrency Issues
Troubleshooting Locking
Troubleshooting Lock Memory
Lock Timeout
Lock Escalation
Troubleshooting Blocking
Detecting Blocking Problems
Finding the Cause of Blocking
Resolving Blocking Problems
Troubleshooting Deadlocking
Types of Deadlocking
Detecting Deadlocks
Determining the Cause of Deadlocks
Resolving Deadlocks
Troubleshooting Row-Versioning–Based Snapshot-Based Isolation Levels
Shared Locking Issues with Snapshot-Based Isolation Levels
Potential SNAPSHOT Isolation Level Conflicts
Monitoring the Snapshot Isolation Options
Resolving Snapshot Isolation Problems
Appropriate Uses for Snapshot Isolation
Summary
Additional Resources and References
Index
© Microsoft. All Rights Reserved.