Dam / Fritchey SQL Server 2008 Query Performance Tuning Distilled

1. ed
ISBN: 978-1-4302-1903-3
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 600 Seiten

SQL Server 2008 Query Performance Tuning Distilled presents a direct trouble-shooting methodology for identifying poorly-performing stored procedures and queries, isolating the causes of that poor performance, and fixing the underlying problems. Each chapter is dedicated to one of the top causes of poorly performing queries and shows methods for identifying and dealing with the problems in that chapter's domain. Emphasis is always put upon or placed upon practical methods that you can put to immediate use in your day-to-day work. SQL Server 2008 functionality, tips, and tricks are emphasized in each subject area. Emphasizes the practical. Does not bury readers in theory. Gives readers practical techniques to immediately apply in their daily work. Dedicates a chapter to each of the most common, performance-related problem areas.

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.

Autoren/Hrsg.

Dam, Sajal

Fritchey, Grant

Inhaltsverzeichnis

1;Contents;5
2;About the Author;18
3;About the Technical Reviewer;19
4;Acknowledgments;20
5;Introduction;21
5.1;Who This Book Is For;21
5.2;How This Book Is Structured;22
5.3;Downloading the Code;23
5.4;Contacting the Author;23
6;SQL Query Performance Tuning;24
6.1;The Performance- Tuning Process;25
6.2;Performance vs. Price;30
6.3;Performance Baseline;31
6.4;Where to Focus Efforts;32
6.5;SQL Server Performance Killers;33
6.6;Summary;38
7;System Performance Analysis;39
7.1;Performance Monitor Tool;39
7.2;Dynamic Management Views;41
7.3;Hardware Resource Bottlenecks;42
7.4;Memory Bottleneck Analysis;43
7.5;Memory Bottleneck Resolutions;49
7.6;Disk Bottleneck Analysis;54
7.7;Disk Bottleneck Resolutions;57
7.8;Processor Bottleneck Analysis;65
7.9;Processor Bottleneck Resolutions;67
7.10;Network Bottleneck Analysis;69
7.11;Network Bottleneck Resolutions;70
7.12;SQL Server Overall Performance;71
7.13;Creating a Baseline;75
7.14;System Behavior Analysis Against Baseline;81
7.15;Summary;82
8;SQL Query Performance Analysis;83
8.1;The SQL Profiler Tool;83
8.2;Trace Automation;92
8.3;Combining Trace and Performance Monitor Output;94
8.4;SQL Profiler Recommendations;95
8.5;Query Performance Metrics Without Profiler;98
8.6;Costly Queries;98
8.7;Execution Plans;105
8.8;Query Cost;117
8.9;Summary;122
9;Index Analysis;123
9.1;What Is an Index?;123
9.2;Index Design Recommendations;129
9.3;Clustered Indexes;139
9.4;Nonclustered Indexes;148
9.5;Clustered vs. Nonclustered Indexes;150
9.6;Advanced Indexing Techniques;154
9.7;Special Index Types;169
9.8;Additional Characteristics of Indexes;170
9.9;Summary;172
10;Database Engine Tuning Advisor;173
10.1;Database Engine Tuning Advisor Mechanisms;173
10.2;Database Engine Tuning Advisor Examples;177
10.3;Database Engine Tuning Advisor Limitations;183
10.4;Summary;184
11;Bookmark Lookup Analysis;185
11.1;Purpose of Bookmark Lookups;185
11.2;Drawbacks of Bookmark Lookups;187
11.3;Analyzing the Cause of a Bookmark Lookup;188
11.4;Resolving Bookmark Lookups;191
11.5;Summary;196
12;Statistics Analysis;197
12.1;The Role of Statistics in Query Optimization;197
12.2;Statistics on an Indexed Column;198
12.3;Statistics on a Nonindexed Column;202
12.4;Analyzing Statistics;209
12.5;Statistics Maintenance;215
12.6;Analyzing the Effectiveness of Statistics for a Query;221
12.7;Recommendations;226
12.8;Summary;230
13;Fragmentation Analysis;231
13.1;Causes of Fragmentation;231
13.2;Fragmentation Overhead;239
13.3;Analyzing the Amount of Fragmentation;242
13.4;Fragmentation Resolutions;246
13.5;Significance of the Fill Factor;252
13.6;Automatic Maintenance;255
13.7;Summary;261
14;Execution Plan Cache Analysis;262
14.1;Execution Plan Generation;262
14.2;Execution Plan Caching;272
14.3;Components of the Execution Plan;272
14.4;Aging of the Execution Plan;273
14.5;Analyzing the Execution Plan Cache;273
14.6;Execution Plan Reuse;274
14.7;Query Plan Hash and Query Hash;295
14.8;Execution Plan Cache Recommendations;299
14.9;Summary;302
15;Stored Procedure Recompilation;303
15.1;Benefits and Drawbacks of Recompilation;303
15.2;Identifying the Statement Causing Recompilation;306
15.3;Analyzing Causes of Recompilation;308
15.4;Avoiding Recompilations;318
15.5;Summary;331
16;Query Design Analysis;332
16.1;Query Design Recommendations;332
16.2;Operating on Small Result Sets;333
16.3;Using Indexes Effectively;335
16.4;Avoiding Optimizer Hints;343
16.5;Using Domain and Referential Integrity;348
16.6;Avoiding Resource- Intensive Queries;353
16.7;Reducing the Number of Network Round- Trips;364
16.8;Reducing the Transaction Cost;365
16.9;Summary;369
17;Blocking Analysis;370
17.1;Blocking Fundamentals;370
17.2;Understanding Blocking;371
17.3;Database Locks;376
17.4;Isolation Levels;388
17.5;Effect of Indexes on Locking;400
17.6;Capturing Blocking Information;404
17.7;Blocking Resolutions;409
17.8;Recommendations to Reduce Blocking;412
17.9;Automation to Detect and Collect Blocking Information;413
17.10;Summary;418
18;Deadlock Analysis;419
18.1;Deadlock Fundamentals;419
18.2;Using Error Handling to Catch a Deadlock;421
18.3;Deadlock Analysis;421
18.4;Avoiding Deadlocks;428
18.5;Summary;431
19;Cursor Cost Analysis;432
19.1;Cursor Fundamentals;432
19.2;Cursor Cost Comparison;439
19.3;Default Result Set;445
19.4;Analyzing SQL Server Overhead with Cursors;449
19.5;Cursor Recommendations;453
19.6;Summary;454
20;Database Workload Optimization;455
20.1;Workload Optimization Fundamentals;455
20.2;Workload Optimization Steps;456
20.3;Capturing the Workload;460
20.4;Analyzing the Workload;462
20.5;Identifying the Costliest Query;463
20.6;Determining the Baseline Resource Use of the Costliest Query;465
20.7;Analyzing and Optimizing External Factors;468
20.8;Analyzing the Internal Behavior of the Costliest Query;474
20.9;Optimizing the Costliest Query;476
20.10;Analyzing the Effect on Database Workload;485
20.11;Iterating Through Optimization Phases;487
20.12;Summary;489
21;SQL Server Optimization Checklist;490
21.1;Database Design;490
21.2;Query Design;497
21.3;Configuration Settings;503
21.4;Database Administration;506
21.5;Database Backup;508
21.6;Summary;510
22;Index;511