SQL Server 2008 Query Performance Tuning Distilled (eBook)

Sajal Dam holds a master's of technology degree in computer science from the Indian Institute of Science, Bangalore, and has been working with Microsoft technologies for over 12 years. He has developed an extensive background in designing database applications and managing software development. Sajal also possesses significant experience in troubleshooting and optimizing the performance of Microsoft-based applications, from front-end web pages to back-end databases. While working at Microsoft, Sajal helped many Fortune 500 companies design scalable database solutions and maximize the performance of their database environments. As an IT strategist at Dell, Sajal manages Dell's vast database infrastructure by optimizing not only the databases, but also the database management processes, tools, and use of best practices. He also works closely with the application development teams and vendors, including Microsoft, in analyzing and resolving performance bottlenecks.

Contents 5
About the Author 18
About the Technical Reviewer 19
Acknowledgments 20
Introduction 21
Who This Book Is For 21
How This Book Is Structured 22
Downloading the Code 23
Contacting the Author 23
SQL Query Performance Tuning 24
The Performance- Tuning Process 25
Performance vs. Price 30
Performance Baseline 31
Where to Focus Efforts 32
SQL Server Performance Killers 33
Summary 38
System Performance Analysis 39
Performance Monitor Tool 39
Dynamic Management Views 41
Hardware Resource Bottlenecks 42
Memory Bottleneck Analysis 43
Memory Bottleneck Resolutions 49
Disk Bottleneck Analysis 54
Disk Bottleneck Resolutions 57
Processor Bottleneck Analysis 65
Processor Bottleneck Resolutions 67
Network Bottleneck Analysis 69
Network Bottleneck Resolutions 70
SQL Server Overall Performance 71
Creating a Baseline 75
System Behavior Analysis Against Baseline 81
Summary 82
SQL Query Performance Analysis 83
The SQL Profiler Tool 83
Trace Automation 92
Combining Trace and Performance Monitor Output 94
SQL Profiler Recommendations 95
Query Performance Metrics Without Profiler 98
Costly Queries 98
Execution Plans 105
Query Cost 117
Summary 122
Index Analysis 123
What Is an Index? 123
Index Design Recommendations 129
Clustered Indexes 139
Nonclustered Indexes 148
Clustered vs. Nonclustered Indexes 150
Advanced Indexing Techniques 154
Special Index Types 169
Additional Characteristics of Indexes 170
Summary 172
Database Engine Tuning Advisor 173
Database Engine Tuning Advisor Mechanisms 173
Database Engine Tuning Advisor Examples 177
Database Engine Tuning Advisor Limitations 183
Summary 184
Bookmark Lookup Analysis 185
Purpose of Bookmark Lookups 185
Drawbacks of Bookmark Lookups 187
Analyzing the Cause of a Bookmark Lookup 188
Resolving Bookmark Lookups 191
Summary 196
Statistics Analysis 197
The Role of Statistics in Query Optimization 197
Statistics on an Indexed Column 198
Statistics on a Nonindexed Column 202
Analyzing Statistics 209
Statistics Maintenance 215
Analyzing the Effectiveness of Statistics for a Query 221
Recommendations 226
Summary 230
Fragmentation Analysis 231
Causes of Fragmentation 231
Fragmentation Overhead 239
Analyzing the Amount of Fragmentation 242
Fragmentation Resolutions 246
Significance of the Fill Factor 252
Automatic Maintenance 255
Summary 261
Execution Plan Cache Analysis 262
Execution Plan Generation 262
Execution Plan Caching 272
Components of the Execution Plan 272
Aging of the Execution Plan 273
Analyzing the Execution Plan Cache 273
Execution Plan Reuse 274
Query Plan Hash and Query Hash 295
Execution Plan Cache Recommendations 299
Summary 302
Stored Procedure Recompilation 303
Benefits and Drawbacks of Recompilation 303
Identifying the Statement Causing Recompilation 306
Analyzing Causes of Recompilation 308
Avoiding Recompilations 318
Summary 331
Query Design Analysis 332
Query Design Recommendations 332
Operating on Small Result Sets 333
Using Indexes Effectively 335
Avoiding Optimizer Hints 343
Using Domain and Referential Integrity 348
Avoiding Resource- Intensive Queries 353
Reducing the Number of Network Round- Trips 364
Reducing the Transaction Cost 365
Summary 369
Blocking Analysis 370
Blocking Fundamentals 370
Understanding Blocking 371
Database Locks 376
Isolation Levels 388
Effect of Indexes on Locking 400
Capturing Blocking Information 404
Blocking Resolutions 409
Recommendations to Reduce Blocking 412
Automation to Detect and Collect Blocking Information 413
Summary 418
Deadlock Analysis 419
Deadlock Fundamentals 419
Using Error Handling to Catch a Deadlock 421
Deadlock Analysis 421
Avoiding Deadlocks 428
Summary 431
Cursor Cost Analysis 432
Cursor Fundamentals 432
Cursor Cost Comparison 439
Default Result Set 445
Analyzing SQL Server Overhead with Cursors 449
Cursor Recommendations 453
Summary 454
Database Workload Optimization 455
Workload Optimization Fundamentals 455
Workload Optimization Steps 456
Capturing the Workload 460
Analyzing the Workload 462
Identifying the Costliest Query 463
Determining the Baseline Resource Use of the Costliest Query 465
Analyzing and Optimizing External Factors 468
Analyzing the Internal Behavior of the Costliest Query 474
Optimizing the Costliest Query 476
Analyzing the Effect on Database Workload 485
Iterating Through Optimization Phases 487
Summary 489
SQL Server Optimization Checklist 490
Database Design 490
Query Design 497
Configuration Settings 503
Database Administration 506
Database Backup 508
Summary 510
Index 511