SQL Server Query Performance Tuning
4th Edition
Published on 16. September 2014
XXIX, 612 pages
978-1-4302-6742-3 (ISBN)
Description
Queries not running fast enough? Wondering about the in-memory database features in 2014? Tired of phone calls from frustrated users? Grant Fritchey's book SQL Server Query Performance Tuning is the answer to your SQL Server query performance problems. The book is revised to cover the very latest in performance optimization features and techniques, especially including the newly-added, in-memory database features formerly known under the code name Project Hekaton. This book provides the tools you need to approach your queries with performance in mind.
SQL Server Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You'll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You'll learn to recognize bottlenecks and defuse them before the phone rings. You'll learn some quick solutions too, but emphasis is on designingfor performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server Query Performance Tuning into practice today.
- Covers the in-memory features from Project Hekaton
- Helps establish performance baselines and monitor against them
- Guides in troubleshooting and eliminating of bottlenecks that frustrate users
More details
Other editions
Additional editions
Grant Fritchey
SQL Server Query Performance Tuning
Book
08/2014
4th Edition
Apress
€53.49
Article exhausted; check for reprint
Content
1 - Contents at a Glance [Seite 3]
2 - Contents [Seite 573]
3 - About the Author [Seite 591]
4 - About the Technical Reviewer [Seite 592]
5 - Acknowledgments [Seite 593]
6 - Introduction [Seite 5]
7 - Chapter 1: SQL Query Performance Tuning [Seite 9]
7.1 - The Performance Tuning Process [Seite 10]
7.1.1 - The Core Process [Seite 10]
7.1.2 - Iterating the Process [Seite 13]
7.2 - Performance vs. Price [Seite 16]
7.2.1 - Performance Targets [Seite 16]
7.2.2 - "Good Enough" Tuning [Seite 16]
7.3 - Performance Baseline [Seite 17]
7.4 - Where to Focus Efforts [Seite 18]
7.5 - SQL Server Performance Killers [Seite 19]
7.5.1 - Insufficient Indexing [Seite 19]
7.5.2 - Inaccurate Statistics [Seite 20]
7.5.3 - Improper Query Design [Seite 20]
7.5.4 - Poorly Generated Execution Plans [Seite 20]
7.5.5 - Excessive Blocking and Deadlocks [Seite 21]
7.5.6 - Non-Set-Based Operations [Seite 21]
7.5.7 - Inappropriate Database Design [Seite 21]
7.5.8 - Excessive Fragmentation [Seite 22]
7.5.9 - Nonreusable Execution Plans [Seite 22]
7.5.10 - Frequent Recompilation of Queries [Seite 22]
7.5.11 - Improper Use of Cursors [Seite 22]
7.5.12 - Improper Configuration of the Database Transaction Log [Seite 22]
7.5.13 - Excessive Use or Improper Configuration of tempdb [Seite 23]
7.6 - Summary [Seite 23]
8 - Chapter 2: Memory Performance Analysis [Seite 24]
8.1 - Performance Monitor Tool [Seite 24]
8.2 - Dynamic Management Objects [Seite 26]
8.3 - Hardware Resource Bottlenecks [Seite 27]
8.3.1 - Identifying Bottlenecks [Seite 27]
8.3.2 - Bottleneck Resolution [Seite 28]
8.4 - Memory Bottleneck Analysis [Seite 28]
8.4.1 - SQL Server Memory Management [Seite 28]
8.4.2 - Available Bytes [Seite 32]
8.4.3 - Pages/Sec and Page Faults/Sec [Seite 32]
8.4.4 - Paging File %Usage and Page File %Usage [Seite 32]
8.4.5 - Buffer Cache Hit Ratio [Seite 33]
8.4.6 - Page Life Expectancy [Seite 33]
8.4.7 - Checkpoint Pages/Sec [Seite 33]
8.4.8 - Lazy Writes/Sec [Seite 34]
8.4.9 - Memory Grants Pending [Seite 34]
8.4.10 - Target Server Memory (KB) and Total Server Memory (KB) [Seite 34]
8.5 - Additional Memory Monitoring Tools [Seite 34]
8.5.1 - DBCC MEMORYSTATUS [Seite 34]
8.5.2 - Dynamic Management Objects [Seite 36]
8.5.2.1 - Sys.dm_os_memory_brokers [Seite 36]
8.5.2.2 - Sys.dm_os_memory_clerks [Seite 36]
8.5.2.3 - Sys.dm_os_ring_buffers [Seite 36]
8.5.2.4 - Sys.dm_db_xtp_table_memory_stats [Seite 37]
8.5.2.5 - Sys.dm_xtp_system_memory_consumers [Seite 37]
8.6 - Memory Bottleneck Resolutions [Seite 37]
8.6.1 - Optimizing Application Workload [Seite 39]
8.6.2 - Allocating More Memory to SQL Server [Seite 39]
8.6.3 - Moving In-Memory Tables Back to Standard Storage [Seite 39]
8.6.4 - Increasing System Memory [Seite 40]
8.6.5 - Changing from a 32-Bit to a 64-Bit Processor [Seite 40]
8.6.6 - Compressing Data [Seite 40]
8.6.7 - Enabling 3GB of Process Address Space [Seite 40]
8.6.8 - Addressing Fragmentation [Seite 41]
8.7 - Summary [Seite 41]
9 - Chapter 3: Disk Performance Analysis [Seite 42]
9.1 - Disk Bottleneck Analysis [Seite 42]
9.1.1 - Disk Counters [Seite 42]
9.1.2 - % Disk Time [Seite 43]
9.1.3 - Current Disk Queue Length [Seite 43]
9.1.4 - Disk Transfers/Sec [Seite 44]
9.1.5 - Disk Bytes/Sec [Seite 44]
9.1.6 - Avg. Disk Sec/Read and Avg. Disk Sec/Write [Seite 44]
9.2 - Additional I/O Monitoring Tools [Seite 44]
9.2.1 - Sys.dm_io_virtual_file_stats [Seite 44]
9.2.2 - Sys.dm_os_wait_stats [Seite 45]
9.3 - Disk Bottleneck Resolutions [Seite 45]
9.3.1 - Optimizing Application Workload [Seite 46]
9.3.2 - Using a Faster I/O Path [Seite 46]
9.3.3 - Using a RAID Array [Seite 46]
9.3.3.1 - RAID 0 [Seite 47]
9.3.3.2 - RAID 1 [Seite 47]
9.3.3.3 - RAID 5 [Seite 47]
9.3.3.4 - RAID 6 [Seite 48]
9.3.3.5 - RAID 1+0 (RAID 10) [Seite 48]
9.3.4 - Using a SAN System [Seite 48]
9.3.5 - Using Solid State Drives [Seite 48]
9.3.6 - Aligning Disks Properly [Seite 49]
9.3.7 - Adding System Memory [Seite 49]
9.3.8 - Creating Multiple Files and Filegroups [Seite 49]
9.3.9 - Moving the Log Files to a Separate Physical Disk [Seite 52]
9.3.10 - Using Partitioned Tables [Seite 52]
9.4 - Summary [Seite 53]
10 - Chapter 4: CPU Performance Analysis [Seite 54]
10.1 - Processor Bottleneck Analysis [Seite 54]
10.1.1 - % Processor Time [Seite 55]
10.1.2 - % Privileged Time [Seite 55]
10.1.3 - Processor Queue Length [Seite 55]
10.1.4 - Context Switches/Sec [Seite 55]
10.1.5 - Batch Requests/Sec [Seite 56]
10.1.6 - SQL Compilations/Sec [Seite 56]
10.1.7 - SQL Recompilations/Sec [Seite 56]
10.2 - Other Tools for Measuring CPU Performance [Seite 56]
10.2.1 - Sys.dm_os_wait_stats [Seite 56]
10.2.2 - Sys.dm_os_workers and Sys.dm_os_schedulers [Seite 57]
10.3 - Processor Bottleneck Resolutions [Seite 57]
10.3.1 - Optimizing Application Workload [Seite 57]
10.3.2 - Eliminating Excessive Compiles/Recompiles [Seite 57]
10.3.3 - Using More or Faster Processors [Seite 57]
10.3.4 - Not Running Unnecessary Software [Seite 58]
10.4 - Network Bottleneck Analysis [Seite 58]
10.4.1 - Bytes Total/Sec [Seite 58]
10.4.2 - % Net Utilization [Seite 58]
10.5 - Network Bottleneck Resolutions [Seite 59]
10.5.1 - Optimizing Application Workload [Seite 59]
10.6 - SQL Server Overall Performance [Seite 59]
10.6.1 - Missing Indexes [Seite 60]
10.6.1.1 - Full Scans/Sec [Seite 60]
10.6.1.2 - Dynamic Management Objects [Seite 61]
10.6.2 - Database Concurrency [Seite 61]
10.6.2.1 - Total Latch Wait Time (Ms) [Seite 61]
10.6.2.2 - Lock Timeouts/Sec and Lock Wait Time (Ms) [Seite 62]
10.6.2.3 - Number of Deadlocks/Sec [Seite 62]
10.6.3 - Nonreusable Execution Plans [Seite 62]
10.6.4 - General Behavior [Seite 62]
10.6.5 - User Connections [Seite 63]
10.6.6 - Batch Requests/Sec [Seite 63]
10.7 - Summary [Seite 63]
11 - Chapter 5: Creating a Baseline [Seite 64]
11.1 - Considerations for Monitoring Virtual and Hosted Machines [Seite 64]
11.2 - Creating a Baseline [Seite 65]
11.2.1 - Creating a Reusable List of Performance Counters [Seite 65]
11.2.2 - Creating a Counter Log Using the List of Performance Counters [Seite 68]
11.2.3 - Performance Monitor Considerations [Seite 71]
11.2.3.1 - Limit the Number of Counters [Seite 72]
11.2.3.2 - Prefer Counter Logs [Seite 72]
11.2.3.3 - View Performance Monitor Graphs Remotely [Seite 72]
11.2.3.4 - Save Counter Log Locally [Seite 72]
11.2.3.5 - Increase the Sampling Interval [Seite 72]
11.2.4 - System Behavior Analysis Against Baseline [Seite 73]
11.3 - Summary [Seite 75]
12 - Chapter 6: Query Performance Metrics [Seite 76]
12.1 - Extended Events [Seite 76]
12.1.1 - Extended Events Sessions [Seite 77]
12.1.2 - Global Fields [Seite 81]
12.1.3 - Event Filters [Seite 82]
12.1.4 - Event Fields [Seite 83]
12.1.5 - Data Storage [Seite 85]
12.1.6 - Finishing the Session [Seite 86]
12.2 - Extended Events Automation [Seite 87]
12.2.1 - Creating a Session Script Using the GUI [Seite 87]
12.2.2 - Defining a Session Using T-SQL [Seite 88]
12.3 - Extended Events Recommendations [Seite 89]
12.3.1 - Set Max File Size Appropriately [Seite 89]
12.3.2 - Avoid Debug Events [Seite 89]
12.3.3 - Avoid Use of No_Event_Loss [Seite 90]
12.4 - Other Methods for Query Performance Metrics [Seite 90]
12.5 - Summary [Seite 91]
13 - Chapter 7: Analyzing Query Performance [Seite 92]
13.1 - Costly Queries [Seite 92]
13.1.1 - Identifying Costly Queries [Seite 93]
13.1.2 - Costly Queries with a Single Execution [Seite 94]
13.1.3 - Costly Queries with Multiple Executions [Seite 95]
13.1.4 - Identifying Slow-Running Queries [Seite 97]
13.2 - Execution Plans [Seite 98]
13.2.1 - Analyzing a Query Execution Plan [Seite 99]
13.2.2 - Identifying the Costly Steps in an Execution Plan [Seite 102]
13.2.3 - Analyzing Index Effectiveness [Seite 103]
13.2.4 - Analyzing Join Effectiveness [Seite 105]
13.2.5 - Hash Join [Seite 106]
13.2.5.1 - Merge Join [Seite 108]
13.2.5.2 - Nested Loop Join [Seite 109]
13.2.6 - Actual vs. Estimated Execution Plans [Seite 110]
13.2.7 - Plan Cache [Seite 112]
13.2.8 - Query Resource Cost [Seite 112]
13.2.9 - Client Statistics [Seite 112]
13.2.10 - Execution Time [Seite 113]
13.2.11 - STATISTICS IO [Seite 115]
13.3 - Summary [Seite 116]
14 - Chapter 8: Index Architecture and Behavior [Seite 117]
14.1 - What Is an Index? [Seite 117]
14.1.1 - The Benefit of Indexes [Seite 119]
14.1.2 - Index Overhead [Seite 121]
14.2 - Index Design Recommendations [Seite 123]
14.2.1 - Examine the WHERE Clause and JOIN Criteria Columns [Seite 123]
14.2.2 - Use Narrow Indexes [Seite 125]
14.2.3 - Examine Column Uniqueness [Seite 127]
14.2.4 - Examine the Column Data Type [Seite 130]
14.2.5 - Consider Column Order [Seite 130]
14.2.6 - Consider the Type of Index [Seite 134]
14.3 - Clustered Indexes [Seite 134]
14.3.1 - Heap Tables [Seite 134]
14.3.2 - Relationship with Nonclustered Indexes [Seite 135]
14.3.3 - Clustered Index Recommendations [Seite 137]
14.3.3.1 - Create the Clustered Index First [Seite 137]
14.3.3.2 - Keep Clustered Indexes Narrow [Seite 137]
14.3.3.3 - Rebuild the Clustered Index in a Single Step [Seite 139]
14.3.3.4 - Where Possible, Make the Clustered Index Unique [Seite 139]
14.3.4 - When to Use a Clustered Index [Seite 140]
14.3.4.1 - Accessing the Data Directly [Seite 140]
14.3.4.2 - Retrieving Presorted Data [Seite 140]
14.3.5 - Poor Design Practices for a Clustered Index [Seite 141]
14.3.5.1 - Frequently Updatable Columns [Seite 141]
14.3.5.2 - Wide Keys [Seite 142]
14.4 - Nonclustered Indexes [Seite 143]
14.4.1 - Nonclustered Index Maintenance [Seite 143]
14.4.2 - Defining the Lookup Operation [Seite 143]
14.4.3 - Nonclustered Index Recommendations [Seite 143]
14.4.3.1 - When to Use a Nonclustered Index [Seite 143]
14.4.3.2 - When Not to Use a Nonclustered Index [Seite 144]
14.5 - Clustered vs. Nonclustered Indexes [Seite 144]
14.5.1 - Benefits of a Clustered Index over a Nonclustered Index [Seite 144]
14.5.2 - Benefits of a Nonclustered Index over a Clustered Index [Seite 147]
14.6 - Summary [Seite 148]
15 - Chapter 9: Index Analysis [Seite 149]
15.1 - Advanced Indexing Techniques [Seite 149]
15.1.1 - Covering Indexes [Seite 150]
15.1.2 - A Pseudoclustered Index [Seite 151]
15.1.3 - Recommendations [Seite 151]
15.1.4 - Index Intersections [Seite 152]
15.1.5 - Index Joins [Seite 154]
15.1.6 - Filtered Indexes [Seite 156]
15.1.7 - Indexed Views [Seite 158]
15.1.7.1 - Benefit [Seite 158]
15.1.7.2 - Overhead [Seite 158]
15.1.7.3 - Usage Scenarios [Seite 159]
15.1.8 - Index Compression [Seite 162]
15.1.9 - Columnstore Indexes [Seite 164]
15.2 - Special Index Types [Seite 167]
15.2.1 - Full-Text [Seite 167]
15.2.2 - Spatial [Seite 167]
15.2.3 - XML [Seite 167]
15.3 - Additional Characteristics of Indexes [Seite 168]
15.3.1 - Different Column Sort Order [Seite 168]
15.3.2 - Index on Computed Columns [Seite 168]
15.3.3 - Index on BIT Data Type Columns [Seite 168]
15.3.4 - CREATE INDEX Statement Processed As a Query [Seite 168]
15.3.5 - Parallel Index Creation [Seite 169]
15.3.6 - Online Index Creation [Seite 169]
15.3.7 - Considering the Database Engine Tuning Advisor [Seite 169]
15.4 - Summary [Seite 169]
16 - Chapter 10: Database Engine Tuning Advisor [Seite 170]
16.1 - Database Engine Tuning Advisor Mechanisms [Seite 170]
16.2 - Database Engine Tuning Advisor Examples [Seite 175]
16.2.1 - Tuning a Query [Seite 175]
16.2.2 - Tuning a Trace Workload [Seite 180]
16.2.3 - Tuning from the Procedure Cache [Seite 183]
16.3 - Database Engine Tuning Advisor Limitations [Seite 184]
16.4 - Summary [Seite 185]
17 - Chapter 11: Key Lookups and Solutions [Seite 186]
17.1 - Purpose of Lookups [Seite 186]
17.2 - Drawbacks of Lookups [Seite 188]
17.3 - Analyzing the Cause of a Lookup [Seite 189]
17.4 - Resolving Lookups [Seite 191]
17.4.1 - Using a Clustered Index [Seite 191]
17.4.2 - Using a Covering Index [Seite 191]
17.4.3 - Using an Index Join [Seite 195]
17.5 - Summary [Seite 197]
18 - Chapter 12: Statistics, Data Distribution, and Cardinality [Seite 198]
18.1 - The Role of Statistics in Query Optimization [Seite 198]
18.1.1 - Statistics on an Indexed Column [Seite 199]
18.1.2 - Benefits of Updated Statistics [Seite 200]
18.1.3 - Drawbacks of Outdated Statistics [Seite 203]
18.2 - Statistics on a Nonindexed Column [Seite 205]
18.2.1 - Benefits of Statistics on a Nonindexed Column [Seite 205]
18.2.2 - Drawback of Missing Statistics on a Nonindexed Column [Seite 210]
18.3 - Analyzing Statistics [Seite 214]
18.3.1 - Density [Seite 217]
18.3.2 - Statistics on a Multicolumn Index [Seite 217]
18.3.3 - Statistics on a Filtered Index [Seite 219]
18.3.4 - Cardinality [Seite 221]
18.3.4.1 - Enabling and Disabling the Cardinality Estimator [Seite 223]
18.4 - Statistics Maintenance [Seite 224]
18.4.1 - Automatic Maintenance [Seite 224]
18.4.2 - Auto Create Statistics [Seite 225]
18.4.3 - Auto Update Statistics [Seite 225]
18.4.4 - Auto Update Statistics Asynchronously [Seite 227]
18.4.5 - Manual Maintenance [Seite 228]
18.4.6 - Manage Statistics Settings [Seite 229]
18.4.7 - Generate Statistics [Seite 230]
18.5 - Statistics Maintenance Status [Seite 230]
18.5.1 - Status of Auto Create Statistics [Seite 231]
18.5.2 - Status of Auto Update Statistics [Seite 231]
18.6 - Analyzing the Effectiveness of Statistics for a Query [Seite 231]
18.6.1 - Resolving a Missing Statistics Issue [Seite 232]
18.6.2 - Resolving an Outdated Statistics Issue [Seite 235]
18.7 - Recommendations [Seite 237]
18.7.1 - Backward Compatibility of Statistics [Seite 237]
18.7.2 - Auto Create Statistics [Seite 237]
18.7.3 - Auto Update Statistics [Seite 238]
18.7.4 - Automatic Update Statistics Asynchronously [Seite 240]
18.7.5 - Amount of Sampling to Collect Statistics [Seite 240]
18.8 - Summary [Seite 240]
19 - Chapter 13: Index Fragmentation [Seite 241]
19.1 - Causes of Fragmentation [Seite 241]
19.1.1 - Page Split by an UPDATE Statement [Seite 243]
19.1.2 - Page Split by an INSERT Statement [Seite 248]
19.2 - Fragmentation Overhead [Seite 249]
19.3 - Analyzing the Amount of Fragmentation [Seite 251]
19.4 - Analyzing the Fragmentation of a Small Table [Seite 254]
19.5 - Fragmentation Resolutions [Seite 256]
19.5.1 - Dropping and Re-creating the Index [Seite 256]
19.5.2 - Re-creating the Index with the DROP_EXISTING Clause [Seite 256]
19.5.3 - Executing the ALTER INDEX REBUILD Statement [Seite 257]
19.5.4 - Executing the ALTER INDEX REORGANIZE Statement [Seite 259]
19.5.5 - Defragmentation and Partitions [Seite 261]
19.6 - Significance of the Fill Factor [Seite 262]
19.7 - Automatic Maintenance [Seite 264]
19.8 - Summary [Seite 271]
20 - Chapter 14: Execution Plan Generation [Seite 272]
20.1 - Execution Plan Generation [Seite 272]
20.1.1 - Parser [Seite 274]
20.1.2 - Binding [Seite 274]
20.1.3 - Optimization [Seite 276]
20.1.3.1 - Simplification [Seite 277]
20.1.3.2 - Trivial Plan Match [Seite 278]
20.1.3.3 - Multiple Optimization Phases [Seite 278]
20.1.3.4 - Parallel Plan Optimization [Seite 281]
20.1.4 - Execution Plan Caching [Seite 283]
20.2 - Components of the Execution Plan [Seite 283]
20.2.1 - Query Plan [Seite 284]
20.2.2 - Execution Context [Seite 284]
20.3 - Aging of the Execution Plan [Seite 284]
20.4 - Summary [Seite 284]
21 - Chapter 15: Execution Plan Cache Behavior [Seite 285]
21.1 - Analyzing the Execution Plan Cache [Seite 285]
21.2 - Execution Plan Reuse [Seite 286]
21.3 - Ad Hoc Workload [Seite 287]
21.3.1 - Prepared Workload [Seite 288]
21.3.2 - Plan Reusability of an Ad Hoc Workload [Seite 288]
21.3.2.1 - Optimize for an Ad Hoc Workload [Seite 291]
21.3.2.2 - Simple Parameterization [Seite 292]
21.3.2.3 - Simple Parameterization Limits [Seite 294]
21.3.2.4 - Forced Parameterization [Seite 294]
21.3.3 - Plan Reusability of a Prepared Workload [Seite 296]
21.3.3.1 - Stored Procedures [Seite 297]
21.3.3.2 - Stored Procedures Are Compiled on First Execution [Seite 300]
21.3.3.3 - Other Performance Benefits of Stored Procedures [Seite 300]
21.3.3.4 - Additional Benefits of Stored Procedures [Seite 301]
21.3.3.5 - sp_executesql [Seite 302]
21.3.3.6 - Prepare/Execute Model [Seite 304]
21.4 - Query Plan Hash and Query Hash [Seite 305]
21.5 - Execution Plan Cache Recommendations [Seite 308]
21.5.1 - Explicitly Parameterize Variable Parts of a Query [Seite 308]
21.5.2 - Create Stored Procedures to Implement Business Functionality [Seite 308]
21.5.3 - Code with sp_executesql to Avoid Stored Procedure Maintenance [Seite 308]
21.5.4 - Implement the Prepare/Execute Model to Avoid Resending a Query String [Seite 309]
21.5.5 - Avoid Ad Hoc Queries [Seite 309]
21.5.6 - Prefer sp_executesql Over EXECUTE for Dynamic Queries [Seite 309]
21.5.7 - Parameterize Variable Parts of Queries with Care [Seite 310]
21.5.8 - Do Not Allow Implicit Resolution of Objects in Queries [Seite 310]
21.6 - Summary [Seite 311]
22 - Chapter 16: Parameter Sniffing [Seite 312]
22.1 - Parameter Sniffing [Seite 312]
22.1.1 - Bad Parameter Sniffing [Seite 315]
22.1.1.1 - Identifying Bad Parameter Sniffing [Seite 317]
22.1.2 - Mitigating Bad Parameter Sniffing [Seite 318]
22.2 - Summary [Seite 320]
23 - Chapter 17: Query Recompilation [Seite 321]
23.1 - Benefits and Drawbacks of Recompilation [Seite 321]
23.2 - Identifying the Statement Causing Recompilation [Seite 324]
23.3 - Analyzing Causes of Recompilation [Seite 326]
23.3.1 - Schema or Bindings Changes [Seite 328]
23.3.2 - Statistics Changes [Seite 328]
23.3.3 - Deferred Object Resolution [Seite 331]
23.3.3.1 - Recompilation Because of a Regular Table [Seite 332]
23.3.3.2 - Recompilation Because of a Local Temporary Table [Seite 333]
23.3.4 - SET Options Changes [Seite 335]
23.3.5 - Execution Plan Aging [Seite 336]
23.3.6 - Explicit Call to sp_recompile [Seite 336]
23.3.7 - Explicit Use of RECOMPILE [Seite 337]
23.3.7.1 - RECOMPILE Clause with the CREATE PROCEDURE Statement [Seite 338]
23.3.7.2 - RECOMPILE Clause with the EXECUTE Statement [Seite 339]
23.3.7.3 - RECOMPILE Hints to Control Individual Statements [Seite 339]
23.4 - Avoiding Recompilations [Seite 340]
23.4.1 - Don't Interleave DDL and DML Statements [Seite 340]
23.4.2 - Avoiding Recompilations Caused by Statistics Change [Seite 342]
23.4.3 - Using the KEEPFIXED PLAN Option [Seite 342]
23.4.4 - Disable Auto Update Statistics on the Table [Seite 344]
23.4.5 - Using Table Variables [Seite 344]
23.4.6 - Avoiding Changing SET Options Within a Stored Procedure [Seite 346]
23.4.7 - Using OPTIMIZE FOR Query Hint [Seite 347]
23.4.8 - Using Plan Guides [Seite 349]
23.5 - Summary [Seite 354]
24 - Chapter 18: Query Design Analysis [Seite 355]
24.1 - Query Design Recommendations [Seite 355]
24.2 - Operating on Small Result Sets [Seite 356]
24.2.1 - Limit the Number of Columns in select_list [Seite 356]
24.2.2 - Use Highly Selective WHERE Clauses [Seite 357]
24.3 - Using Indexes Effectively [Seite 357]
24.3.1 - Avoid Nonsargable Search Conditions [Seite 358]
24.3.1.1 - BETWEEN vs. IN/OR [Seite 358]
24.3.1.2 - LIKE Condition [Seite 360]
24.3.1.3 - !< Condition vs. >=Condition [Seite 361]
24.3.2 - Avoid Arithmetic Operators on the WHERE Clause Column [Seite 362]
24.3.3 - Avoid Functions on the WHERE Clause Column [Seite 364]
24.3.3.1 - SUBSTRING vs. LIKE [Seite 364]
24.3.3.2 - Date Part Comparison [Seite 365]
24.4 - Avoiding Optimizer Hints [Seite 366]
24.4.1 - JOIN Hint [Seite 367]
24.4.2 - INDEX Hints [Seite 370]
24.5 - Using Domain and Referential Integrity [Seite 372]
24.5.1 - NOT NULL Constraint [Seite 372]
24.5.2 - Declarative Referential Integrity [Seite 375]
24.6 - Summary [Seite 377]
25 - Chapter 19: Reduce Query Resource Use [Seite 378]
25.1 - Avoiding Resource-Intensive Queries [Seite 378]
25.1.1 - Avoid Data Type Conversion [Seite 378]
25.1.2 - Use EXISTS over COUNT(*) to Verify Data Existence [Seite 381]
25.1.3 - Use UNION ALL Instead of UNION [Seite 382]
25.1.4 - Use Indexes for Aggregate and Sort Conditions [Seite 383]
25.1.5 - Avoid Local Variables in a Batch Query [Seite 384]
25.1.6 - Be Careful When Naming Stored Procedures [Seite 388]
25.2 - Reducing the Number of Network Round-Trips [Seite 390]
25.2.1 - Execute Multiple Queries Together [Seite 390]
25.2.2 - Use SET NOCOUNT [Seite 391]
25.3 - Reducing the Transaction Cost [Seite 391]
25.3.1 - Reduce Logging Overhead [Seite 391]
25.3.2 - Reduce Lock Overhead [Seite 393]
25.4 - Summary [Seite 394]
26 - Chapter 20: Blocking and Blocked Processes [Seite 395]
26.1 - Blocking Fundamentals [Seite 395]
26.2 - Understanding Blocking [Seite 396]
26.2.1 - Atomicity [Seite 396]
26.2.1.1 - SET XACT_ABORT ON [Seite 398]
26.2.1.2 - Explicit Rollback [Seite 398]
26.2.2 - Consistency [Seite 399]
26.2.3 - Isolation [Seite 399]
26.2.4 - Durability [Seite 400]
26.3 - Locks [Seite 401]
26.3.1 - Lock Granularity [Seite 401]
26.3.2 - Row-Level Lock [Seite 402]
26.3.2.1 - Key-Level Lock [Seite 403]
26.3.3 - Page-Level Lock [Seite 404]
26.3.4 - Extent-Level Lock [Seite 404]
26.3.5 - Heap or B-tree Lock [Seite 405]
26.3.6 - Table-Level Lock [Seite 405]
26.3.7 - Database-Level Lock [Seite 405]
26.4 - Lock Operations and Modes [Seite 406]
26.4.1 - Lock Escalation [Seite 406]
26.4.2 - Lock Modes [Seite 406]
26.4.2.1 - Shared (S) Mode [Seite 407]
26.4.2.2 - Update (U) Mode [Seite 407]
26.4.3 - Exclusive (X) Mode [Seite 411]
26.4.4 - Intent Shared (IS), Intent Exclusive (IX and Shared with Intent Exclusive (SIX) Modes [Seite 411]
26.4.5 - Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes [Seite 412]
26.4.6 - Bulk Update (BU) Mode [Seite 412]
26.4.7 - Key-range Mode [Seite 413]
26.4.8 - Lock Compatibility [Seite 413]
26.5 - Isolation Levels [Seite 413]
26.5.1 - Read Uncommitted [Seite 414]
26.5.2 - Read Committed [Seite 414]
26.5.3 - Repeatable Read [Seite 415]
26.5.4 - Serializable [Seite 418]
26.5.5 - Snapshot [Seite 423]
26.6 - Effect of Indexes on Locking [Seite 423]
26.6.1 - Effect of a Nonclustered Index [Seite 424]
26.6.2 - Effect of a Clustered Index [Seite 426]
26.6.3 - Effect of Indexes on the Serializable Isolation Level [Seite 427]
26.7 - Capturing Blocking Information [Seite 427]
26.7.1 - Capturing Blocking Information with SQL [Seite 428]
26.7.2 - Extended Events and the blocked_process_report Event [Seite 430]
26.8 - Blocking Resolutions [Seite 432]
26.8.1 - Optimize the Queries [Seite 433]
26.8.2 - Decrease the Isolation Level [Seite 433]
26.9 - Partition the Contended Data [Seite 434]
26.10 - Recommendations to Reduce Blocking [Seite 435]
26.11 - Automation to Detect and Collect Blocking Information [Seite 436]
26.12 - Summary [Seite 439]
27 - Chapter 21: Causes and Solutions for Deadlocks [Seite 440]
27.1 - Deadlock Fundamentals [Seite 440]
27.1.1 - Choosing the Deadlock Victim [Seite 441]
27.1.2 - Using Error Handling to Catch a Deadlock [Seite 442]
27.2 - Deadlock Analysis [Seite 443]
27.2.1 - Collecting Deadlock Information [Seite 443]
27.2.2 - Analyzing the Deadlock [Seite 445]
27.3 - Avoiding Deadlocks [Seite 452]
27.3.1 - Accessing Resources in the Same Physical Order [Seite 452]
27.3.2 - Decreasing the Number of Resources Accessed [Seite 453]
27.3.2.1 - Convert a Nonclustered Index to a Clustered Index [Seite 453]
27.3.2.2 - Use a Covering Index for a SELECT Statement [Seite 453]
27.3.3 - Minimizing Lock Contention [Seite 453]
27.3.3.1 - Implement Row Versioning [Seite 454]
27.3.3.2 - Decrease the Isolation Level [Seite 454]
27.3.3.3 - Use Locking Hints [Seite 454]
27.4 - Summary [Seite 455]
28 - Chapter 22: Row-by-Row Processing [Seite 456]
28.1 - Cursor Fundamentals [Seite 456]
28.1.1 - Cursor Location [Seite 458]
28.1.1.1 - Client-Side Cursors [Seite 458]
28.1.1.2 - Server-Side Cursors [Seite 459]
28.1.2 - Cursor Concurrency [Seite 459]
28.1.2.1 - Read-Only [Seite 459]
28.1.2.2 - Optimistic [Seite 459]
28.1.3 - Cursor Types [Seite 460]
28.1.3.1 - Forward-Only Cursors [Seite 461]
28.1.3.2 - Static Cursors [Seite 461]
28.1.3.3 - Keyset-Driven Cursors [Seite 462]
28.1.3.4 - Dynamic Cursors [Seite 463]
28.1.4 - Cursor Cost Comparison [Seite 463]
28.1.5 - Cost Comparison on Cursor Location [Seite 463]
28.1.5.1 - Client-Side Cursors [Seite 463]
28.1.5.2 - Server-Side Cursors [Seite 464]
28.1.6 - Cost Comparison on Cursor Concurrency [Seite 465]
28.1.7 - Read-Only [Seite 465]
28.1.7.1 - Optimistic [Seite 465]
28.1.7.2 - Scroll Locks [Seite 466]
28.1.8 - Cost Comparison on Cursor Type [Seite 466]
28.1.8.1 - Forward-Only Cursors [Seite 467]
28.1.8.2 - Fast-Forward-Only Cursor [Seite 467]
28.1.8.3 - Static Cursors [Seite 467]
28.1.8.4 - Keyset-Driven Cursors [Seite 468]
28.1.8.5 - Dynamic Cursor [Seite 468]
28.2 - Default Result Set [Seite 469]
28.2.1 - Benefits [Seite 469]
28.2.2 - Multiple Active Result Sets [Seite 469]
28.2.3 - Drawbacks [Seite 470]
28.3 - Cursor Overhead [Seite 472]
28.3.1 - Analyzing Overhead with T-SQL Cursors [Seite 473]
28.3.2 - Cursor Recommendations [Seite 477]
28.4 - Summary [Seite 478]
29 - Chapter 23: Memory-Optimized OLTP Tables and Procedures [Seite 479]
29.1 - In-Memory OLTP Fundamentals [Seite 479]
29.1.1 - System Requirements [Seite 480]
29.1.2 - Basic Setup [Seite 480]
29.1.3 - Create Tables [Seite 481]
29.1.4 - In-Memory Indexes [Seite 486]
29.1.5 - Hash Index [Seite 486]
29.1.5.1 - Nonclustered Indexes [Seite 488]
29.1.5.2 - Index Maintenance [Seite 490]
29.2 - Natively Compiled Stored Procedures [Seite 491]
29.3 - Recommendations [Seite 493]
29.3.1 - Baselines [Seite 494]
29.3.2 - Correct Workload [Seite 494]
29.3.3 - Memory Optimization Advisor [Seite 494]
29.3.4 - Native Compilation Advisor [Seite 498]
29.4 - Summary [Seite 500]
30 - Chapter 24: Database Performance Testing [Seite 501]
30.1 - Database Performance Testing [Seite 501]
30.1.1 - A Repeatable Process [Seite 502]
30.1.2 - Distributed Replay [Seite 502]
30.2 - Capturing Data with the Server-Side Trace [Seite 503]
30.3 - Distributed Replay for Database Testing [Seite 507]
30.3.1 - Configuring the Client [Seite 508]
30.3.2 - Running the Distributed Tests [Seite 508]
30.4 - Conclusion [Seite 509]
31 - Chapter 25: Database Workload Optimization [Seite 510]
31.1 - Workload Optimization Fundamentals [Seite 510]
31.1.1 - Workload Optimization Steps [Seite 511]
31.1.2 - Sample Workload [Seite 512]
31.2 - Capturing the Workload [Seite 515]
31.3 - Analyzing the Workload [Seite 515]
31.4 - Identifying the Costliest Query [Seite 517]
31.4.1 - Determining the Baseline Resource Use of the Costliest Query [Seite 518]
31.4.2 - Overall Resource Use [Seite 519]
31.4.3 - Detailed Resource Use [Seite 519]
31.5 - Analyzing and Optimizing External Factors [Seite 521]
31.5.1 - Analyzing the Connection Options Used by the Application [Seite 522]
31.5.2 - Analyzing the Effectiveness of Statistics [Seite 522]
31.5.3 - Analyzing the Need for Defragmentation [Seite 523]
31.5.4 - Analyzing the Internal Behavior of the Costliest Query [Seite 527]
31.5.5 - Analyzing the Query Execution Plan [Seite 528]
31.5.6 - Identifying the Costly Steps in the Execution Plan [Seite 529]
31.5.7 - Analyzing the Processing Strategy [Seite 529]
31.5.8 - Optimizing the Costliest Query [Seite 530]
31.5.9 - Modifying the Code [Seite 530]
31.5.10 - Fixing the Key Lookup Operation [Seite 533]
31.5.11 - Tuning the Second Query [Seite 534]
31.5.12 - Creating a Wrapper Procedure [Seite 536]
31.6 - Analyzing the Effect on Database Workload [Seite 537]
31.7 - Iterating Through Optimization Phases [Seite 538]
31.8 - Summary [Seite 541]
32 - Chapter 26: SQL Server Optimization Checklist [Seite 542]
32.1 - Database Design [Seite 542]
32.1.1 - Balancing Under- and Overnormalization [Seite 543]
32.1.2 - Benefiting from Entity-Integrity Constraints [Seite 544]
32.1.3 - Benefiting from Domain and Referential Integrity Constraints [Seite 546]
32.1.4 - Adopting Index-Design Best Practices [Seite 547]
32.1.5 - Avoiding the Use of the sp_ Prefix for Stored Procedure Names [Seite 549]
32.1.6 - Minimizing the Use of Triggers [Seite 549]
32.1.7 - Consider Putting Tables into In-Memory Storage [Seite 549]
32.2 - Configuration Settings [Seite 549]
32.2.1 - Memory Configuration Options [Seite 550]
32.2.2 - Cost Threshold for Parallelism [Seite 550]
32.2.3 - Max Degree of Parallelism [Seite 550]
32.2.4 - Optimize for Ad Hoc Workloads [Seite 550]
32.2.5 - Blocked Process Threshold [Seite 550]
32.2.6 - Database File Layout [Seite 551]
32.2.7 - Database Compression [Seite 551]
32.3 - Database Administration [Seite 551]
32.3.1 - Keep the Statistics Up-to-Date [Seite 552]
32.3.2 - Maintain a Minimum Amount of Index Defragmentation [Seite 552]
32.3.3 - Avoid Database Functions Such As AUTO_CLOSE or AUTO_ SHRINK [Seite 552]
32.4 - Database Backup [Seite 553]
32.4.1 - Incremental and Transaction Log Backup Frequency [Seite 553]
32.4.2 - Backup Scheduling Distribution [Seite 553]
32.4.3 - Backup Compression [Seite 554]
32.5 - Query Design [Seite 554]
32.5.1 - Use the Command SET NOCOUNT ON [Seite 555]
32.5.2 - Explicitly Define the Owner of an Object [Seite 555]
32.5.3 - Avoid Nonsargable Search Conditions [Seite 555]
32.5.4 - Avoid Arithmetic Expressions on the WHERE Clause Column [Seite 556]
32.5.5 - Avoid Optimizer Hints [Seite 556]
32.5.6 - Stay Away from Nesting Views [Seite 557]
32.5.7 - Ensure No Implicit Data Type Conversions [Seite 557]
32.5.8 - Minimize Logging Overhead [Seite 557]
32.5.9 - Adopt Best Practices for Reusing Execution Plans [Seite 557]
32.5.9.1 - Caching Execution Plans Effectively [Seite 558]
32.5.9.2 - Minimizing Recompilation of Execution Plans [Seite 558]
32.5.10 - Adopt Best Practices for Database Transactions [Seite 558]
32.5.11 - Eliminate or Reduce the Overhead of Database Cursors [Seite 559]
32.5.12 - Natively Compile Stored Procedures [Seite 559]
32.6 - Summary [Seite 559]
33 - Index [Seite 560]
2 - Contents [Seite 573]
3 - About the Author [Seite 591]
4 - About the Technical Reviewer [Seite 592]
5 - Acknowledgments [Seite 593]
6 - Introduction [Seite 5]
7 - Chapter 1: SQL Query Performance Tuning [Seite 9]
7.1 - The Performance Tuning Process [Seite 10]
7.1.1 - The Core Process [Seite 10]
7.1.2 - Iterating the Process [Seite 13]
7.2 - Performance vs. Price [Seite 16]
7.2.1 - Performance Targets [Seite 16]
7.2.2 - "Good Enough" Tuning [Seite 16]
7.3 - Performance Baseline [Seite 17]
7.4 - Where to Focus Efforts [Seite 18]
7.5 - SQL Server Performance Killers [Seite 19]
7.5.1 - Insufficient Indexing [Seite 19]
7.5.2 - Inaccurate Statistics [Seite 20]
7.5.3 - Improper Query Design [Seite 20]
7.5.4 - Poorly Generated Execution Plans [Seite 20]
7.5.5 - Excessive Blocking and Deadlocks [Seite 21]
7.5.6 - Non-Set-Based Operations [Seite 21]
7.5.7 - Inappropriate Database Design [Seite 21]
7.5.8 - Excessive Fragmentation [Seite 22]
7.5.9 - Nonreusable Execution Plans [Seite 22]
7.5.10 - Frequent Recompilation of Queries [Seite 22]
7.5.11 - Improper Use of Cursors [Seite 22]
7.5.12 - Improper Configuration of the Database Transaction Log [Seite 22]
7.5.13 - Excessive Use or Improper Configuration of tempdb [Seite 23]
7.6 - Summary [Seite 23]
8 - Chapter 2: Memory Performance Analysis [Seite 24]
8.1 - Performance Monitor Tool [Seite 24]
8.2 - Dynamic Management Objects [Seite 26]
8.3 - Hardware Resource Bottlenecks [Seite 27]
8.3.1 - Identifying Bottlenecks [Seite 27]
8.3.2 - Bottleneck Resolution [Seite 28]
8.4 - Memory Bottleneck Analysis [Seite 28]
8.4.1 - SQL Server Memory Management [Seite 28]
8.4.2 - Available Bytes [Seite 32]
8.4.3 - Pages/Sec and Page Faults/Sec [Seite 32]
8.4.4 - Paging File %Usage and Page File %Usage [Seite 32]
8.4.5 - Buffer Cache Hit Ratio [Seite 33]
8.4.6 - Page Life Expectancy [Seite 33]
8.4.7 - Checkpoint Pages/Sec [Seite 33]
8.4.8 - Lazy Writes/Sec [Seite 34]
8.4.9 - Memory Grants Pending [Seite 34]
8.4.10 - Target Server Memory (KB) and Total Server Memory (KB) [Seite 34]
8.5 - Additional Memory Monitoring Tools [Seite 34]
8.5.1 - DBCC MEMORYSTATUS [Seite 34]
8.5.2 - Dynamic Management Objects [Seite 36]
8.5.2.1 - Sys.dm_os_memory_brokers [Seite 36]
8.5.2.2 - Sys.dm_os_memory_clerks [Seite 36]
8.5.2.3 - Sys.dm_os_ring_buffers [Seite 36]
8.5.2.4 - Sys.dm_db_xtp_table_memory_stats [Seite 37]
8.5.2.5 - Sys.dm_xtp_system_memory_consumers [Seite 37]
8.6 - Memory Bottleneck Resolutions [Seite 37]
8.6.1 - Optimizing Application Workload [Seite 39]
8.6.2 - Allocating More Memory to SQL Server [Seite 39]
8.6.3 - Moving In-Memory Tables Back to Standard Storage [Seite 39]
8.6.4 - Increasing System Memory [Seite 40]
8.6.5 - Changing from a 32-Bit to a 64-Bit Processor [Seite 40]
8.6.6 - Compressing Data [Seite 40]
8.6.7 - Enabling 3GB of Process Address Space [Seite 40]
8.6.8 - Addressing Fragmentation [Seite 41]
8.7 - Summary [Seite 41]
9 - Chapter 3: Disk Performance Analysis [Seite 42]
9.1 - Disk Bottleneck Analysis [Seite 42]
9.1.1 - Disk Counters [Seite 42]
9.1.2 - % Disk Time [Seite 43]
9.1.3 - Current Disk Queue Length [Seite 43]
9.1.4 - Disk Transfers/Sec [Seite 44]
9.1.5 - Disk Bytes/Sec [Seite 44]
9.1.6 - Avg. Disk Sec/Read and Avg. Disk Sec/Write [Seite 44]
9.2 - Additional I/O Monitoring Tools [Seite 44]
9.2.1 - Sys.dm_io_virtual_file_stats [Seite 44]
9.2.2 - Sys.dm_os_wait_stats [Seite 45]
9.3 - Disk Bottleneck Resolutions [Seite 45]
9.3.1 - Optimizing Application Workload [Seite 46]
9.3.2 - Using a Faster I/O Path [Seite 46]
9.3.3 - Using a RAID Array [Seite 46]
9.3.3.1 - RAID 0 [Seite 47]
9.3.3.2 - RAID 1 [Seite 47]
9.3.3.3 - RAID 5 [Seite 47]
9.3.3.4 - RAID 6 [Seite 48]
9.3.3.5 - RAID 1+0 (RAID 10) [Seite 48]
9.3.4 - Using a SAN System [Seite 48]
9.3.5 - Using Solid State Drives [Seite 48]
9.3.6 - Aligning Disks Properly [Seite 49]
9.3.7 - Adding System Memory [Seite 49]
9.3.8 - Creating Multiple Files and Filegroups [Seite 49]
9.3.9 - Moving the Log Files to a Separate Physical Disk [Seite 52]
9.3.10 - Using Partitioned Tables [Seite 52]
9.4 - Summary [Seite 53]
10 - Chapter 4: CPU Performance Analysis [Seite 54]
10.1 - Processor Bottleneck Analysis [Seite 54]
10.1.1 - % Processor Time [Seite 55]
10.1.2 - % Privileged Time [Seite 55]
10.1.3 - Processor Queue Length [Seite 55]
10.1.4 - Context Switches/Sec [Seite 55]
10.1.5 - Batch Requests/Sec [Seite 56]
10.1.6 - SQL Compilations/Sec [Seite 56]
10.1.7 - SQL Recompilations/Sec [Seite 56]
10.2 - Other Tools for Measuring CPU Performance [Seite 56]
10.2.1 - Sys.dm_os_wait_stats [Seite 56]
10.2.2 - Sys.dm_os_workers and Sys.dm_os_schedulers [Seite 57]
10.3 - Processor Bottleneck Resolutions [Seite 57]
10.3.1 - Optimizing Application Workload [Seite 57]
10.3.2 - Eliminating Excessive Compiles/Recompiles [Seite 57]
10.3.3 - Using More or Faster Processors [Seite 57]
10.3.4 - Not Running Unnecessary Software [Seite 58]
10.4 - Network Bottleneck Analysis [Seite 58]
10.4.1 - Bytes Total/Sec [Seite 58]
10.4.2 - % Net Utilization [Seite 58]
10.5 - Network Bottleneck Resolutions [Seite 59]
10.5.1 - Optimizing Application Workload [Seite 59]
10.6 - SQL Server Overall Performance [Seite 59]
10.6.1 - Missing Indexes [Seite 60]
10.6.1.1 - Full Scans/Sec [Seite 60]
10.6.1.2 - Dynamic Management Objects [Seite 61]
10.6.2 - Database Concurrency [Seite 61]
10.6.2.1 - Total Latch Wait Time (Ms) [Seite 61]
10.6.2.2 - Lock Timeouts/Sec and Lock Wait Time (Ms) [Seite 62]
10.6.2.3 - Number of Deadlocks/Sec [Seite 62]
10.6.3 - Nonreusable Execution Plans [Seite 62]
10.6.4 - General Behavior [Seite 62]
10.6.5 - User Connections [Seite 63]
10.6.6 - Batch Requests/Sec [Seite 63]
10.7 - Summary [Seite 63]
11 - Chapter 5: Creating a Baseline [Seite 64]
11.1 - Considerations for Monitoring Virtual and Hosted Machines [Seite 64]
11.2 - Creating a Baseline [Seite 65]
11.2.1 - Creating a Reusable List of Performance Counters [Seite 65]
11.2.2 - Creating a Counter Log Using the List of Performance Counters [Seite 68]
11.2.3 - Performance Monitor Considerations [Seite 71]
11.2.3.1 - Limit the Number of Counters [Seite 72]
11.2.3.2 - Prefer Counter Logs [Seite 72]
11.2.3.3 - View Performance Monitor Graphs Remotely [Seite 72]
11.2.3.4 - Save Counter Log Locally [Seite 72]
11.2.3.5 - Increase the Sampling Interval [Seite 72]
11.2.4 - System Behavior Analysis Against Baseline [Seite 73]
11.3 - Summary [Seite 75]
12 - Chapter 6: Query Performance Metrics [Seite 76]
12.1 - Extended Events [Seite 76]
12.1.1 - Extended Events Sessions [Seite 77]
12.1.2 - Global Fields [Seite 81]
12.1.3 - Event Filters [Seite 82]
12.1.4 - Event Fields [Seite 83]
12.1.5 - Data Storage [Seite 85]
12.1.6 - Finishing the Session [Seite 86]
12.2 - Extended Events Automation [Seite 87]
12.2.1 - Creating a Session Script Using the GUI [Seite 87]
12.2.2 - Defining a Session Using T-SQL [Seite 88]
12.3 - Extended Events Recommendations [Seite 89]
12.3.1 - Set Max File Size Appropriately [Seite 89]
12.3.2 - Avoid Debug Events [Seite 89]
12.3.3 - Avoid Use of No_Event_Loss [Seite 90]
12.4 - Other Methods for Query Performance Metrics [Seite 90]
12.5 - Summary [Seite 91]
13 - Chapter 7: Analyzing Query Performance [Seite 92]
13.1 - Costly Queries [Seite 92]
13.1.1 - Identifying Costly Queries [Seite 93]
13.1.2 - Costly Queries with a Single Execution [Seite 94]
13.1.3 - Costly Queries with Multiple Executions [Seite 95]
13.1.4 - Identifying Slow-Running Queries [Seite 97]
13.2 - Execution Plans [Seite 98]
13.2.1 - Analyzing a Query Execution Plan [Seite 99]
13.2.2 - Identifying the Costly Steps in an Execution Plan [Seite 102]
13.2.3 - Analyzing Index Effectiveness [Seite 103]
13.2.4 - Analyzing Join Effectiveness [Seite 105]
13.2.5 - Hash Join [Seite 106]
13.2.5.1 - Merge Join [Seite 108]
13.2.5.2 - Nested Loop Join [Seite 109]
13.2.6 - Actual vs. Estimated Execution Plans [Seite 110]
13.2.7 - Plan Cache [Seite 112]
13.2.8 - Query Resource Cost [Seite 112]
13.2.9 - Client Statistics [Seite 112]
13.2.10 - Execution Time [Seite 113]
13.2.11 - STATISTICS IO [Seite 115]
13.3 - Summary [Seite 116]
14 - Chapter 8: Index Architecture and Behavior [Seite 117]
14.1 - What Is an Index? [Seite 117]
14.1.1 - The Benefit of Indexes [Seite 119]
14.1.2 - Index Overhead [Seite 121]
14.2 - Index Design Recommendations [Seite 123]
14.2.1 - Examine the WHERE Clause and JOIN Criteria Columns [Seite 123]
14.2.2 - Use Narrow Indexes [Seite 125]
14.2.3 - Examine Column Uniqueness [Seite 127]
14.2.4 - Examine the Column Data Type [Seite 130]
14.2.5 - Consider Column Order [Seite 130]
14.2.6 - Consider the Type of Index [Seite 134]
14.3 - Clustered Indexes [Seite 134]
14.3.1 - Heap Tables [Seite 134]
14.3.2 - Relationship with Nonclustered Indexes [Seite 135]
14.3.3 - Clustered Index Recommendations [Seite 137]
14.3.3.1 - Create the Clustered Index First [Seite 137]
14.3.3.2 - Keep Clustered Indexes Narrow [Seite 137]
14.3.3.3 - Rebuild the Clustered Index in a Single Step [Seite 139]
14.3.3.4 - Where Possible, Make the Clustered Index Unique [Seite 139]
14.3.4 - When to Use a Clustered Index [Seite 140]
14.3.4.1 - Accessing the Data Directly [Seite 140]
14.3.4.2 - Retrieving Presorted Data [Seite 140]
14.3.5 - Poor Design Practices for a Clustered Index [Seite 141]
14.3.5.1 - Frequently Updatable Columns [Seite 141]
14.3.5.2 - Wide Keys [Seite 142]
14.4 - Nonclustered Indexes [Seite 143]
14.4.1 - Nonclustered Index Maintenance [Seite 143]
14.4.2 - Defining the Lookup Operation [Seite 143]
14.4.3 - Nonclustered Index Recommendations [Seite 143]
14.4.3.1 - When to Use a Nonclustered Index [Seite 143]
14.4.3.2 - When Not to Use a Nonclustered Index [Seite 144]
14.5 - Clustered vs. Nonclustered Indexes [Seite 144]
14.5.1 - Benefits of a Clustered Index over a Nonclustered Index [Seite 144]
14.5.2 - Benefits of a Nonclustered Index over a Clustered Index [Seite 147]
14.6 - Summary [Seite 148]
15 - Chapter 9: Index Analysis [Seite 149]
15.1 - Advanced Indexing Techniques [Seite 149]
15.1.1 - Covering Indexes [Seite 150]
15.1.2 - A Pseudoclustered Index [Seite 151]
15.1.3 - Recommendations [Seite 151]
15.1.4 - Index Intersections [Seite 152]
15.1.5 - Index Joins [Seite 154]
15.1.6 - Filtered Indexes [Seite 156]
15.1.7 - Indexed Views [Seite 158]
15.1.7.1 - Benefit [Seite 158]
15.1.7.2 - Overhead [Seite 158]
15.1.7.3 - Usage Scenarios [Seite 159]
15.1.8 - Index Compression [Seite 162]
15.1.9 - Columnstore Indexes [Seite 164]
15.2 - Special Index Types [Seite 167]
15.2.1 - Full-Text [Seite 167]
15.2.2 - Spatial [Seite 167]
15.2.3 - XML [Seite 167]
15.3 - Additional Characteristics of Indexes [Seite 168]
15.3.1 - Different Column Sort Order [Seite 168]
15.3.2 - Index on Computed Columns [Seite 168]
15.3.3 - Index on BIT Data Type Columns [Seite 168]
15.3.4 - CREATE INDEX Statement Processed As a Query [Seite 168]
15.3.5 - Parallel Index Creation [Seite 169]
15.3.6 - Online Index Creation [Seite 169]
15.3.7 - Considering the Database Engine Tuning Advisor [Seite 169]
15.4 - Summary [Seite 169]
16 - Chapter 10: Database Engine Tuning Advisor [Seite 170]
16.1 - Database Engine Tuning Advisor Mechanisms [Seite 170]
16.2 - Database Engine Tuning Advisor Examples [Seite 175]
16.2.1 - Tuning a Query [Seite 175]
16.2.2 - Tuning a Trace Workload [Seite 180]
16.2.3 - Tuning from the Procedure Cache [Seite 183]
16.3 - Database Engine Tuning Advisor Limitations [Seite 184]
16.4 - Summary [Seite 185]
17 - Chapter 11: Key Lookups and Solutions [Seite 186]
17.1 - Purpose of Lookups [Seite 186]
17.2 - Drawbacks of Lookups [Seite 188]
17.3 - Analyzing the Cause of a Lookup [Seite 189]
17.4 - Resolving Lookups [Seite 191]
17.4.1 - Using a Clustered Index [Seite 191]
17.4.2 - Using a Covering Index [Seite 191]
17.4.3 - Using an Index Join [Seite 195]
17.5 - Summary [Seite 197]
18 - Chapter 12: Statistics, Data Distribution, and Cardinality [Seite 198]
18.1 - The Role of Statistics in Query Optimization [Seite 198]
18.1.1 - Statistics on an Indexed Column [Seite 199]
18.1.2 - Benefits of Updated Statistics [Seite 200]
18.1.3 - Drawbacks of Outdated Statistics [Seite 203]
18.2 - Statistics on a Nonindexed Column [Seite 205]
18.2.1 - Benefits of Statistics on a Nonindexed Column [Seite 205]
18.2.2 - Drawback of Missing Statistics on a Nonindexed Column [Seite 210]
18.3 - Analyzing Statistics [Seite 214]
18.3.1 - Density [Seite 217]
18.3.2 - Statistics on a Multicolumn Index [Seite 217]
18.3.3 - Statistics on a Filtered Index [Seite 219]
18.3.4 - Cardinality [Seite 221]
18.3.4.1 - Enabling and Disabling the Cardinality Estimator [Seite 223]
18.4 - Statistics Maintenance [Seite 224]
18.4.1 - Automatic Maintenance [Seite 224]
18.4.2 - Auto Create Statistics [Seite 225]
18.4.3 - Auto Update Statistics [Seite 225]
18.4.4 - Auto Update Statistics Asynchronously [Seite 227]
18.4.5 - Manual Maintenance [Seite 228]
18.4.6 - Manage Statistics Settings [Seite 229]
18.4.7 - Generate Statistics [Seite 230]
18.5 - Statistics Maintenance Status [Seite 230]
18.5.1 - Status of Auto Create Statistics [Seite 231]
18.5.2 - Status of Auto Update Statistics [Seite 231]
18.6 - Analyzing the Effectiveness of Statistics for a Query [Seite 231]
18.6.1 - Resolving a Missing Statistics Issue [Seite 232]
18.6.2 - Resolving an Outdated Statistics Issue [Seite 235]
18.7 - Recommendations [Seite 237]
18.7.1 - Backward Compatibility of Statistics [Seite 237]
18.7.2 - Auto Create Statistics [Seite 237]
18.7.3 - Auto Update Statistics [Seite 238]
18.7.4 - Automatic Update Statistics Asynchronously [Seite 240]
18.7.5 - Amount of Sampling to Collect Statistics [Seite 240]
18.8 - Summary [Seite 240]
19 - Chapter 13: Index Fragmentation [Seite 241]
19.1 - Causes of Fragmentation [Seite 241]
19.1.1 - Page Split by an UPDATE Statement [Seite 243]
19.1.2 - Page Split by an INSERT Statement [Seite 248]
19.2 - Fragmentation Overhead [Seite 249]
19.3 - Analyzing the Amount of Fragmentation [Seite 251]
19.4 - Analyzing the Fragmentation of a Small Table [Seite 254]
19.5 - Fragmentation Resolutions [Seite 256]
19.5.1 - Dropping and Re-creating the Index [Seite 256]
19.5.2 - Re-creating the Index with the DROP_EXISTING Clause [Seite 256]
19.5.3 - Executing the ALTER INDEX REBUILD Statement [Seite 257]
19.5.4 - Executing the ALTER INDEX REORGANIZE Statement [Seite 259]
19.5.5 - Defragmentation and Partitions [Seite 261]
19.6 - Significance of the Fill Factor [Seite 262]
19.7 - Automatic Maintenance [Seite 264]
19.8 - Summary [Seite 271]
20 - Chapter 14: Execution Plan Generation [Seite 272]
20.1 - Execution Plan Generation [Seite 272]
20.1.1 - Parser [Seite 274]
20.1.2 - Binding [Seite 274]
20.1.3 - Optimization [Seite 276]
20.1.3.1 - Simplification [Seite 277]
20.1.3.2 - Trivial Plan Match [Seite 278]
20.1.3.3 - Multiple Optimization Phases [Seite 278]
20.1.3.4 - Parallel Plan Optimization [Seite 281]
20.1.4 - Execution Plan Caching [Seite 283]
20.2 - Components of the Execution Plan [Seite 283]
20.2.1 - Query Plan [Seite 284]
20.2.2 - Execution Context [Seite 284]
20.3 - Aging of the Execution Plan [Seite 284]
20.4 - Summary [Seite 284]
21 - Chapter 15: Execution Plan Cache Behavior [Seite 285]
21.1 - Analyzing the Execution Plan Cache [Seite 285]
21.2 - Execution Plan Reuse [Seite 286]
21.3 - Ad Hoc Workload [Seite 287]
21.3.1 - Prepared Workload [Seite 288]
21.3.2 - Plan Reusability of an Ad Hoc Workload [Seite 288]
21.3.2.1 - Optimize for an Ad Hoc Workload [Seite 291]
21.3.2.2 - Simple Parameterization [Seite 292]
21.3.2.3 - Simple Parameterization Limits [Seite 294]
21.3.2.4 - Forced Parameterization [Seite 294]
21.3.3 - Plan Reusability of a Prepared Workload [Seite 296]
21.3.3.1 - Stored Procedures [Seite 297]
21.3.3.2 - Stored Procedures Are Compiled on First Execution [Seite 300]
21.3.3.3 - Other Performance Benefits of Stored Procedures [Seite 300]
21.3.3.4 - Additional Benefits of Stored Procedures [Seite 301]
21.3.3.5 - sp_executesql [Seite 302]
21.3.3.6 - Prepare/Execute Model [Seite 304]
21.4 - Query Plan Hash and Query Hash [Seite 305]
21.5 - Execution Plan Cache Recommendations [Seite 308]
21.5.1 - Explicitly Parameterize Variable Parts of a Query [Seite 308]
21.5.2 - Create Stored Procedures to Implement Business Functionality [Seite 308]
21.5.3 - Code with sp_executesql to Avoid Stored Procedure Maintenance [Seite 308]
21.5.4 - Implement the Prepare/Execute Model to Avoid Resending a Query String [Seite 309]
21.5.5 - Avoid Ad Hoc Queries [Seite 309]
21.5.6 - Prefer sp_executesql Over EXECUTE for Dynamic Queries [Seite 309]
21.5.7 - Parameterize Variable Parts of Queries with Care [Seite 310]
21.5.8 - Do Not Allow Implicit Resolution of Objects in Queries [Seite 310]
21.6 - Summary [Seite 311]
22 - Chapter 16: Parameter Sniffing [Seite 312]
22.1 - Parameter Sniffing [Seite 312]
22.1.1 - Bad Parameter Sniffing [Seite 315]
22.1.1.1 - Identifying Bad Parameter Sniffing [Seite 317]
22.1.2 - Mitigating Bad Parameter Sniffing [Seite 318]
22.2 - Summary [Seite 320]
23 - Chapter 17: Query Recompilation [Seite 321]
23.1 - Benefits and Drawbacks of Recompilation [Seite 321]
23.2 - Identifying the Statement Causing Recompilation [Seite 324]
23.3 - Analyzing Causes of Recompilation [Seite 326]
23.3.1 - Schema or Bindings Changes [Seite 328]
23.3.2 - Statistics Changes [Seite 328]
23.3.3 - Deferred Object Resolution [Seite 331]
23.3.3.1 - Recompilation Because of a Regular Table [Seite 332]
23.3.3.2 - Recompilation Because of a Local Temporary Table [Seite 333]
23.3.4 - SET Options Changes [Seite 335]
23.3.5 - Execution Plan Aging [Seite 336]
23.3.6 - Explicit Call to sp_recompile [Seite 336]
23.3.7 - Explicit Use of RECOMPILE [Seite 337]
23.3.7.1 - RECOMPILE Clause with the CREATE PROCEDURE Statement [Seite 338]
23.3.7.2 - RECOMPILE Clause with the EXECUTE Statement [Seite 339]
23.3.7.3 - RECOMPILE Hints to Control Individual Statements [Seite 339]
23.4 - Avoiding Recompilations [Seite 340]
23.4.1 - Don't Interleave DDL and DML Statements [Seite 340]
23.4.2 - Avoiding Recompilations Caused by Statistics Change [Seite 342]
23.4.3 - Using the KEEPFIXED PLAN Option [Seite 342]
23.4.4 - Disable Auto Update Statistics on the Table [Seite 344]
23.4.5 - Using Table Variables [Seite 344]
23.4.6 - Avoiding Changing SET Options Within a Stored Procedure [Seite 346]
23.4.7 - Using OPTIMIZE FOR Query Hint [Seite 347]
23.4.8 - Using Plan Guides [Seite 349]
23.5 - Summary [Seite 354]
24 - Chapter 18: Query Design Analysis [Seite 355]
24.1 - Query Design Recommendations [Seite 355]
24.2 - Operating on Small Result Sets [Seite 356]
24.2.1 - Limit the Number of Columns in select_list [Seite 356]
24.2.2 - Use Highly Selective WHERE Clauses [Seite 357]
24.3 - Using Indexes Effectively [Seite 357]
24.3.1 - Avoid Nonsargable Search Conditions [Seite 358]
24.3.1.1 - BETWEEN vs. IN/OR [Seite 358]
24.3.1.2 - LIKE Condition [Seite 360]
24.3.1.3 - !< Condition vs. >=Condition [Seite 361]
24.3.2 - Avoid Arithmetic Operators on the WHERE Clause Column [Seite 362]
24.3.3 - Avoid Functions on the WHERE Clause Column [Seite 364]
24.3.3.1 - SUBSTRING vs. LIKE [Seite 364]
24.3.3.2 - Date Part Comparison [Seite 365]
24.4 - Avoiding Optimizer Hints [Seite 366]
24.4.1 - JOIN Hint [Seite 367]
24.4.2 - INDEX Hints [Seite 370]
24.5 - Using Domain and Referential Integrity [Seite 372]
24.5.1 - NOT NULL Constraint [Seite 372]
24.5.2 - Declarative Referential Integrity [Seite 375]
24.6 - Summary [Seite 377]
25 - Chapter 19: Reduce Query Resource Use [Seite 378]
25.1 - Avoiding Resource-Intensive Queries [Seite 378]
25.1.1 - Avoid Data Type Conversion [Seite 378]
25.1.2 - Use EXISTS over COUNT(*) to Verify Data Existence [Seite 381]
25.1.3 - Use UNION ALL Instead of UNION [Seite 382]
25.1.4 - Use Indexes for Aggregate and Sort Conditions [Seite 383]
25.1.5 - Avoid Local Variables in a Batch Query [Seite 384]
25.1.6 - Be Careful When Naming Stored Procedures [Seite 388]
25.2 - Reducing the Number of Network Round-Trips [Seite 390]
25.2.1 - Execute Multiple Queries Together [Seite 390]
25.2.2 - Use SET NOCOUNT [Seite 391]
25.3 - Reducing the Transaction Cost [Seite 391]
25.3.1 - Reduce Logging Overhead [Seite 391]
25.3.2 - Reduce Lock Overhead [Seite 393]
25.4 - Summary [Seite 394]
26 - Chapter 20: Blocking and Blocked Processes [Seite 395]
26.1 - Blocking Fundamentals [Seite 395]
26.2 - Understanding Blocking [Seite 396]
26.2.1 - Atomicity [Seite 396]
26.2.1.1 - SET XACT_ABORT ON [Seite 398]
26.2.1.2 - Explicit Rollback [Seite 398]
26.2.2 - Consistency [Seite 399]
26.2.3 - Isolation [Seite 399]
26.2.4 - Durability [Seite 400]
26.3 - Locks [Seite 401]
26.3.1 - Lock Granularity [Seite 401]
26.3.2 - Row-Level Lock [Seite 402]
26.3.2.1 - Key-Level Lock [Seite 403]
26.3.3 - Page-Level Lock [Seite 404]
26.3.4 - Extent-Level Lock [Seite 404]
26.3.5 - Heap or B-tree Lock [Seite 405]
26.3.6 - Table-Level Lock [Seite 405]
26.3.7 - Database-Level Lock [Seite 405]
26.4 - Lock Operations and Modes [Seite 406]
26.4.1 - Lock Escalation [Seite 406]
26.4.2 - Lock Modes [Seite 406]
26.4.2.1 - Shared (S) Mode [Seite 407]
26.4.2.2 - Update (U) Mode [Seite 407]
26.4.3 - Exclusive (X) Mode [Seite 411]
26.4.4 - Intent Shared (IS), Intent Exclusive (IX and Shared with Intent Exclusive (SIX) Modes [Seite 411]
26.4.5 - Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes [Seite 412]
26.4.6 - Bulk Update (BU) Mode [Seite 412]
26.4.7 - Key-range Mode [Seite 413]
26.4.8 - Lock Compatibility [Seite 413]
26.5 - Isolation Levels [Seite 413]
26.5.1 - Read Uncommitted [Seite 414]
26.5.2 - Read Committed [Seite 414]
26.5.3 - Repeatable Read [Seite 415]
26.5.4 - Serializable [Seite 418]
26.5.5 - Snapshot [Seite 423]
26.6 - Effect of Indexes on Locking [Seite 423]
26.6.1 - Effect of a Nonclustered Index [Seite 424]
26.6.2 - Effect of a Clustered Index [Seite 426]
26.6.3 - Effect of Indexes on the Serializable Isolation Level [Seite 427]
26.7 - Capturing Blocking Information [Seite 427]
26.7.1 - Capturing Blocking Information with SQL [Seite 428]
26.7.2 - Extended Events and the blocked_process_report Event [Seite 430]
26.8 - Blocking Resolutions [Seite 432]
26.8.1 - Optimize the Queries [Seite 433]
26.8.2 - Decrease the Isolation Level [Seite 433]
26.9 - Partition the Contended Data [Seite 434]
26.10 - Recommendations to Reduce Blocking [Seite 435]
26.11 - Automation to Detect and Collect Blocking Information [Seite 436]
26.12 - Summary [Seite 439]
27 - Chapter 21: Causes and Solutions for Deadlocks [Seite 440]
27.1 - Deadlock Fundamentals [Seite 440]
27.1.1 - Choosing the Deadlock Victim [Seite 441]
27.1.2 - Using Error Handling to Catch a Deadlock [Seite 442]
27.2 - Deadlock Analysis [Seite 443]
27.2.1 - Collecting Deadlock Information [Seite 443]
27.2.2 - Analyzing the Deadlock [Seite 445]
27.3 - Avoiding Deadlocks [Seite 452]
27.3.1 - Accessing Resources in the Same Physical Order [Seite 452]
27.3.2 - Decreasing the Number of Resources Accessed [Seite 453]
27.3.2.1 - Convert a Nonclustered Index to a Clustered Index [Seite 453]
27.3.2.2 - Use a Covering Index for a SELECT Statement [Seite 453]
27.3.3 - Minimizing Lock Contention [Seite 453]
27.3.3.1 - Implement Row Versioning [Seite 454]
27.3.3.2 - Decrease the Isolation Level [Seite 454]
27.3.3.3 - Use Locking Hints [Seite 454]
27.4 - Summary [Seite 455]
28 - Chapter 22: Row-by-Row Processing [Seite 456]
28.1 - Cursor Fundamentals [Seite 456]
28.1.1 - Cursor Location [Seite 458]
28.1.1.1 - Client-Side Cursors [Seite 458]
28.1.1.2 - Server-Side Cursors [Seite 459]
28.1.2 - Cursor Concurrency [Seite 459]
28.1.2.1 - Read-Only [Seite 459]
28.1.2.2 - Optimistic [Seite 459]
28.1.3 - Cursor Types [Seite 460]
28.1.3.1 - Forward-Only Cursors [Seite 461]
28.1.3.2 - Static Cursors [Seite 461]
28.1.3.3 - Keyset-Driven Cursors [Seite 462]
28.1.3.4 - Dynamic Cursors [Seite 463]
28.1.4 - Cursor Cost Comparison [Seite 463]
28.1.5 - Cost Comparison on Cursor Location [Seite 463]
28.1.5.1 - Client-Side Cursors [Seite 463]
28.1.5.2 - Server-Side Cursors [Seite 464]
28.1.6 - Cost Comparison on Cursor Concurrency [Seite 465]
28.1.7 - Read-Only [Seite 465]
28.1.7.1 - Optimistic [Seite 465]
28.1.7.2 - Scroll Locks [Seite 466]
28.1.8 - Cost Comparison on Cursor Type [Seite 466]
28.1.8.1 - Forward-Only Cursors [Seite 467]
28.1.8.2 - Fast-Forward-Only Cursor [Seite 467]
28.1.8.3 - Static Cursors [Seite 467]
28.1.8.4 - Keyset-Driven Cursors [Seite 468]
28.1.8.5 - Dynamic Cursor [Seite 468]
28.2 - Default Result Set [Seite 469]
28.2.1 - Benefits [Seite 469]
28.2.2 - Multiple Active Result Sets [Seite 469]
28.2.3 - Drawbacks [Seite 470]
28.3 - Cursor Overhead [Seite 472]
28.3.1 - Analyzing Overhead with T-SQL Cursors [Seite 473]
28.3.2 - Cursor Recommendations [Seite 477]
28.4 - Summary [Seite 478]
29 - Chapter 23: Memory-Optimized OLTP Tables and Procedures [Seite 479]
29.1 - In-Memory OLTP Fundamentals [Seite 479]
29.1.1 - System Requirements [Seite 480]
29.1.2 - Basic Setup [Seite 480]
29.1.3 - Create Tables [Seite 481]
29.1.4 - In-Memory Indexes [Seite 486]
29.1.5 - Hash Index [Seite 486]
29.1.5.1 - Nonclustered Indexes [Seite 488]
29.1.5.2 - Index Maintenance [Seite 490]
29.2 - Natively Compiled Stored Procedures [Seite 491]
29.3 - Recommendations [Seite 493]
29.3.1 - Baselines [Seite 494]
29.3.2 - Correct Workload [Seite 494]
29.3.3 - Memory Optimization Advisor [Seite 494]
29.3.4 - Native Compilation Advisor [Seite 498]
29.4 - Summary [Seite 500]
30 - Chapter 24: Database Performance Testing [Seite 501]
30.1 - Database Performance Testing [Seite 501]
30.1.1 - A Repeatable Process [Seite 502]
30.1.2 - Distributed Replay [Seite 502]
30.2 - Capturing Data with the Server-Side Trace [Seite 503]
30.3 - Distributed Replay for Database Testing [Seite 507]
30.3.1 - Configuring the Client [Seite 508]
30.3.2 - Running the Distributed Tests [Seite 508]
30.4 - Conclusion [Seite 509]
31 - Chapter 25: Database Workload Optimization [Seite 510]
31.1 - Workload Optimization Fundamentals [Seite 510]
31.1.1 - Workload Optimization Steps [Seite 511]
31.1.2 - Sample Workload [Seite 512]
31.2 - Capturing the Workload [Seite 515]
31.3 - Analyzing the Workload [Seite 515]
31.4 - Identifying the Costliest Query [Seite 517]
31.4.1 - Determining the Baseline Resource Use of the Costliest Query [Seite 518]
31.4.2 - Overall Resource Use [Seite 519]
31.4.3 - Detailed Resource Use [Seite 519]
31.5 - Analyzing and Optimizing External Factors [Seite 521]
31.5.1 - Analyzing the Connection Options Used by the Application [Seite 522]
31.5.2 - Analyzing the Effectiveness of Statistics [Seite 522]
31.5.3 - Analyzing the Need for Defragmentation [Seite 523]
31.5.4 - Analyzing the Internal Behavior of the Costliest Query [Seite 527]
31.5.5 - Analyzing the Query Execution Plan [Seite 528]
31.5.6 - Identifying the Costly Steps in the Execution Plan [Seite 529]
31.5.7 - Analyzing the Processing Strategy [Seite 529]
31.5.8 - Optimizing the Costliest Query [Seite 530]
31.5.9 - Modifying the Code [Seite 530]
31.5.10 - Fixing the Key Lookup Operation [Seite 533]
31.5.11 - Tuning the Second Query [Seite 534]
31.5.12 - Creating a Wrapper Procedure [Seite 536]
31.6 - Analyzing the Effect on Database Workload [Seite 537]
31.7 - Iterating Through Optimization Phases [Seite 538]
31.8 - Summary [Seite 541]
32 - Chapter 26: SQL Server Optimization Checklist [Seite 542]
32.1 - Database Design [Seite 542]
32.1.1 - Balancing Under- and Overnormalization [Seite 543]
32.1.2 - Benefiting from Entity-Integrity Constraints [Seite 544]
32.1.3 - Benefiting from Domain and Referential Integrity Constraints [Seite 546]
32.1.4 - Adopting Index-Design Best Practices [Seite 547]
32.1.5 - Avoiding the Use of the sp_ Prefix for Stored Procedure Names [Seite 549]
32.1.6 - Minimizing the Use of Triggers [Seite 549]
32.1.7 - Consider Putting Tables into In-Memory Storage [Seite 549]
32.2 - Configuration Settings [Seite 549]
32.2.1 - Memory Configuration Options [Seite 550]
32.2.2 - Cost Threshold for Parallelism [Seite 550]
32.2.3 - Max Degree of Parallelism [Seite 550]
32.2.4 - Optimize for Ad Hoc Workloads [Seite 550]
32.2.5 - Blocked Process Threshold [Seite 550]
32.2.6 - Database File Layout [Seite 551]
32.2.7 - Database Compression [Seite 551]
32.3 - Database Administration [Seite 551]
32.3.1 - Keep the Statistics Up-to-Date [Seite 552]
32.3.2 - Maintain a Minimum Amount of Index Defragmentation [Seite 552]
32.3.3 - Avoid Database Functions Such As AUTO_CLOSE or AUTO_ SHRINK [Seite 552]
32.4 - Database Backup [Seite 553]
32.4.1 - Incremental and Transaction Log Backup Frequency [Seite 553]
32.4.2 - Backup Scheduling Distribution [Seite 553]
32.4.3 - Backup Compression [Seite 554]
32.5 - Query Design [Seite 554]
32.5.1 - Use the Command SET NOCOUNT ON [Seite 555]
32.5.2 - Explicitly Define the Owner of an Object [Seite 555]
32.5.3 - Avoid Nonsargable Search Conditions [Seite 555]
32.5.4 - Avoid Arithmetic Expressions on the WHERE Clause Column [Seite 556]
32.5.5 - Avoid Optimizer Hints [Seite 556]
32.5.6 - Stay Away from Nesting Views [Seite 557]
32.5.7 - Ensure No Implicit Data Type Conversions [Seite 557]
32.5.8 - Minimize Logging Overhead [Seite 557]
32.5.9 - Adopt Best Practices for Reusing Execution Plans [Seite 557]
32.5.9.1 - Caching Execution Plans Effectively [Seite 558]
32.5.9.2 - Minimizing Recompilation of Execution Plans [Seite 558]
32.5.10 - Adopt Best Practices for Database Transactions [Seite 558]
32.5.11 - Eliminate or Reduce the Overhead of Database Cursors [Seite 559]
32.5.12 - Natively Compile Stored Procedures [Seite 559]
32.6 - Summary [Seite 559]
33 - Index [Seite 560]
