Fritchey SQL Server Query Performance Tuning

4th Auflage
ISBN: 978-1-4302-6742-3
Verlag: APRESS
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 593 Seiten

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 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.

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 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

Zielgruppe

Popular/general

Autoren/Hrsg.

Fritchey, Grant

Inhaltsverzeichnis

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