SQL Server Query Performance Tuning (eBook)

Contents at a Glance 3
Contents 573
About the Author 591
About the Technical Reviewer 592
Acknowledgments 593
Introduction 5
Chapter 1: SQL Query Performance Tuning 9
The Performance Tuning Process 10
The Core Process 10
Iterating the Process 13
Performance vs. Price 16
Performance Targets 16
“Good Enough” Tuning 16
Performance Baseline 17
Where to Focus Efforts 18
SQL Server Performance Killers 19
Insufficient Indexing 19
Inaccurate Statistics 20
Improper Query Design 20
Poorly Generated Execution Plans 20
Excessive Blocking and Deadlocks 21
Non-Set-Based Operations 21
Inappropriate Database Design 21
Excessive Fragmentation 22
Nonreusable Execution Plans 22
Frequent Recompilation of Queries 22
Improper Use of Cursors 22
Improper Configuration of the Database Transaction Log 22
Excessive Use or Improper Configuration of tempdb 23
Summary 23
Chapter 2: Memory Performance Analysis 24
Performance Monitor Tool 24
Dynamic Management Objects 26
Hardware Resource Bottlenecks 27
Identifying Bottlenecks 27
Bottleneck Resolution 28
Memory Bottleneck Analysis 28
SQL Server Memory Management 28
Available Bytes 32
Pages/Sec and Page Faults/Sec 32
Paging File %Usage and Page File %Usage 32
Buffer Cache Hit Ratio 33
Page Life Expectancy 33
Checkpoint Pages/Sec 33
Lazy Writes/Sec 34
Memory Grants Pending 34
Target Server Memory (KB) and Total Server Memory (KB) 34
Additional Memory Monitoring Tools 34
DBCC MEMORYSTATUS 34
Dynamic Management Objects 36
Sys.dm_os_memory_brokers 36
Sys.dm_os_memory_clerks 36
Sys.dm_os_ring_buffers 36
Sys.dm_db_xtp_table_memory_stats 37
Sys.dm_xtp_system_memory_consumers 37
Memory Bottleneck Resolutions 37
Optimizing Application Workload 39
Allocating More Memory to SQL Server 39
Moving In-Memory Tables Back to Standard Storage 39
Increasing System Memory 40
Changing from a 32-Bit to a 64-Bit Processor 40
Compressing Data 40
Enabling 3GB of Process Address Space 40
Addressing Fragmentation 41
Summary 41
Chapter 3: Disk Performance Analysis 42
Disk Bottleneck Analysis 42
Disk Counters 42
% Disk Time 43
Current Disk Queue Length 43
Disk Transfers/Sec 44
Disk Bytes/Sec 44
Avg. Disk Sec/Read and Avg. Disk Sec/Write 44
Additional I/O Monitoring Tools 44
Sys.dm_io_virtual_file_stats 44
Sys.dm_os_wait_stats 45
Disk Bottleneck Resolutions 45
Optimizing Application Workload 46
Using a Faster I/O Path 46
Using a RAID Array 46
RAID 0 47
RAID 1 47
RAID 5 47
RAID 6 48
RAID 1+0 (RAID 10) 48
Using a SAN System 48
Using Solid State Drives 48
Aligning Disks Properly 49
Adding System Memory 49
Creating Multiple Files and Filegroups 49
Moving the Log Files to a Separate Physical Disk 52
Using Partitioned Tables 52
Summary 53
Chapter 4: CPU Performance Analysis 54
Processor Bottleneck Analysis 54
% Processor Time 55
% Privileged Time 55
Processor Queue Length 55
Context Switches/Sec 55
Batch Requests/Sec 56
SQL Compilations/Sec 56
SQL Recompilations/Sec 56
Other Tools for Measuring CPU Performance 56
Sys.dm_os_wait_stats 56
Sys.dm_os_workers and Sys.dm_os_schedulers 57
Processor Bottleneck Resolutions 57
Optimizing Application Workload 57
Eliminating Excessive Compiles/Recompiles 57
Using More or Faster Processors 57
Not Running Unnecessary Software 58
Network Bottleneck Analysis 58
Bytes Total/Sec 58
% Net Utilization 58
Network Bottleneck Resolutions 59
Optimizing Application Workload 59
SQL Server Overall Performance 59
Missing Indexes 60
Full Scans/Sec 60
Dynamic Management Objects 61
Database Concurrency 61
Total Latch Wait Time (Ms) 61
Lock Timeouts/Sec and Lock Wait Time (Ms) 62
Number of Deadlocks/Sec 62
Nonreusable Execution Plans 62
General Behavior 62
User Connections 63
Batch Requests/Sec 63
Summary 63
Chapter 5: Creating a Baseline 64
Considerations for Monitoring Virtual and Hosted Machines 64
Creating a Baseline 65
Creating a Reusable List of Performance Counters 65
Creating a Counter Log Using the List of Performance Counters 68
Performance Monitor Considerations 71
Limit the Number of Counters 72
Prefer Counter Logs 72
View Performance Monitor Graphs Remotely 72
Save Counter Log Locally 72
Increase the Sampling Interval 72
System Behavior Analysis Against Baseline 73
Summary 75
Chapter 6: Query Performance Metrics 76
Extended Events 76
Extended Events Sessions 77
Global Fields 81
Event Filters 82
Event Fields 83
Data Storage 85
Finishing the Session 86
Extended Events Automation 87
Creating a Session Script Using the GUI 87
Defining a Session Using T-SQL 88
Extended Events Recommendations 89
Set Max File Size Appropriately 89
Avoid Debug Events 89
Avoid Use of No_Event_Loss 90
Other Methods for Query Performance Metrics 90
Summary 91
Chapter 7: Analyzing Query Performance 92
Costly Queries 92
Identifying Costly Queries 93
Costly Queries with a Single Execution 94
Costly Queries with Multiple Executions 95
Identifying Slow-Running Queries 97
Execution Plans 98
Analyzing a Query Execution Plan 99
Identifying the Costly Steps in an Execution Plan 102
Analyzing Index Effectiveness 103
Analyzing Join Effectiveness 105
Hash Join 106
Merge Join 108
Nested Loop Join 109
Actual vs. Estimated Execution Plans 110
Plan Cache 112
Query Resource Cost 112
Client Statistics 112
Execution Time 113
STATISTICS IO 115
Summary 116
Chapter 8: Index Architecture and Behavior 117
What Is an Index? 117
The Benefit of Indexes 119
Index Overhead 121
Index Design Recommendations 123
Examine the WHERE Clause and JOIN Criteria Columns 123
Use Narrow Indexes 125
Examine Column Uniqueness 127
Examine the Column Data Type 130
Consider Column Order 130
Consider the Type of Index 134
Clustered Indexes 134
Heap Tables 134
Relationship with Nonclustered Indexes 135
Clustered Index Recommendations 137
Create the Clustered Index First 137
Keep Clustered Indexes Narrow 137
Rebuild the Clustered Index in a Single Step 139
Where Possible, Make the Clustered Index Unique 139
When to Use a Clustered Index 140
Accessing the Data Directly 140
Retrieving Presorted Data 140
Poor Design Practices for a Clustered Index 141
Frequently Updatable Columns 141
Wide Keys 142
Nonclustered Indexes 143
Nonclustered Index Maintenance 143
Defining the Lookup Operation 143
Nonclustered Index Recommendations 143
When to Use a Nonclustered Index 143
When Not to Use a Nonclustered Index 144
Clustered vs. Nonclustered Indexes 144
Benefits of a Clustered Index over a Nonclustered Index 144
Benefits of a Nonclustered Index over a Clustered Index 147
Summary 148
Chapter 9: Index Analysis 149
Advanced Indexing Techniques 149
Covering Indexes 150
A Pseudoclustered Index 151
Recommendations 151
Index Intersections 152
Index Joins 154
Filtered Indexes 156
Indexed Views 158
Benefit 158
Overhead 158
Usage Scenarios 159
Index Compression 162
Columnstore Indexes 164
Special Index Types 167
Full-Text 167
Spatial 167
XML 167
Additional Characteristics of Indexes 168
Different Column Sort Order 168
Index on Computed Columns 168
Index on BIT Data Type Columns 168
CREATE INDEX Statement Processed As a Query 168
Parallel Index Creation 169
Online Index Creation 169
Considering the Database Engine Tuning Advisor 169
Summary 169
Chapter 10: Database Engine Tuning Advisor 170
Database Engine Tuning Advisor Mechanisms 170
Database Engine Tuning Advisor Examples 175
Tuning a Query 175
Tuning a Trace Workload 180
Tuning from the Procedure Cache 183
Database Engine Tuning Advisor Limitations 184
Summary 185
Chapter 11: Key Lookups and Solutions 186
Purpose of Lookups 186
Drawbacks of Lookups 188
Analyzing the Cause of a Lookup 189
Resolving Lookups 191
Using a Clustered Index 191
Using a Covering Index 191
Using an Index Join 195
Summary 197
Chapter 12: Statistics, Data Distribution, and Cardinality 198
The Role of Statistics in Query Optimization 198
Statistics on an Indexed Column 199
Benefits of Updated Statistics 200
Drawbacks of Outdated Statistics 203
Statistics on a Nonindexed Column 205
Benefits of Statistics on a Nonindexed Column 205
Drawback of Missing Statistics on a Nonindexed Column 210
Analyzing Statistics 214
Density 217
Statistics on a Multicolumn Index 217
Statistics on a Filtered Index 219
Cardinality 221
Enabling and Disabling the Cardinality Estimator 223
Statistics Maintenance 224
Automatic Maintenance 224
Auto Create Statistics 225
Auto Update Statistics 225
Auto Update Statistics Asynchronously 227
Manual Maintenance 228
Manage Statistics Settings 229
Generate Statistics 230
Statistics Maintenance Status 230
Status of Auto Create Statistics 231
Status of Auto Update Statistics 231
Analyzing the Effectiveness of Statistics for a Query 231
Resolving a Missing Statistics Issue 232
Resolving an Outdated Statistics Issue 235
Recommendations 237
Backward Compatibility of Statistics 237
Auto Create Statistics 237
Auto Update Statistics 238
Automatic Update Statistics Asynchronously 240
Amount of Sampling to Collect Statistics 240
Summary 240
Chapter 13: Index Fragmentation 241
Causes of Fragmentation 241
Page Split by an UPDATE Statement 243
Page Split by an INSERT Statement 248
Fragmentation Overhead 249
Analyzing the Amount of Fragmentation 251
Analyzing the Fragmentation of a Small Table 254
Fragmentation Resolutions 256
Dropping and Re-creating the Index 256
Re-creating the Index with the DROP_EXISTING Clause 256
Executing the ALTER INDEX REBUILD Statement 257
Executing the ALTER INDEX REORGANIZE Statement 259
Defragmentation and Partitions 261
Significance of the Fill Factor 262
Automatic Maintenance 264
Summary 271
Chapter 14: Execution Plan Generation 272
Execution Plan Generation 272
Parser 274
Binding 274
Optimization 276
Simplification 277
Trivial Plan Match 278
Multiple Optimization Phases 278
Parallel Plan Optimization 281
Execution Plan Caching 283
Components of the Execution Plan 283
Query Plan 284
Execution Context 284
Aging of the Execution Plan 284
Summary 284
Chapter 15: Execution Plan Cache Behavior 285
Analyzing the Execution Plan Cache 285
Execution Plan Reuse 286
Ad Hoc Workload 287
Prepared Workload 288
Plan Reusability of an Ad Hoc Workload 288
Optimize for an Ad Hoc Workload 291
Simple Parameterization 292
Simple Parameterization Limits 294
Forced Parameterization 294
Plan Reusability of a Prepared Workload 296
Stored Procedures 297
Stored Procedures Are Compiled on First Execution 300
Other Performance Benefits of Stored Procedures 300
Additional Benefits of Stored Procedures 301
sp_executesql 302
Prepare/Execute Model 304
Query Plan Hash and Query Hash 305
Execution Plan Cache Recommendations 308
Explicitly Parameterize Variable Parts of a Query 308
Create Stored Procedures to Implement Business Functionality 308
Code with sp_executesql to Avoid Stored Procedure Maintenance 308
Implement the Prepare/Execute Model to Avoid Resending a Query String 309
Avoid Ad Hoc Queries 309
Prefer sp_executesql Over EXECUTE for Dynamic Queries 309
Parameterize Variable Parts of Queries with Care 310
Do Not Allow Implicit Resolution of Objects in Queries 310
Summary 311
Chapter 16: Parameter Sniffing 312
Parameter Sniffing 312
Bad Parameter Sniffing 315
Identifying Bad Parameter Sniffing 317
Mitigating Bad Parameter Sniffing 318
Summary 320
Chapter 17: Query Recompilation 321
Benefits and Drawbacks of Recompilation 321
Identifying the Statement Causing Recompilation 324
Analyzing Causes of Recompilation 326
Schema or Bindings Changes 328
Statistics Changes 328
Deferred Object Resolution 331
Recompilation Because of a Regular Table 332
Recompilation Because of a Local Temporary Table 333
SET Options Changes 335
Execution Plan Aging 336
Explicit Call to sp_recompile 336
Explicit Use of RECOMPILE 337
RECOMPILE Clause with the CREATE PROCEDURE Statement 338
RECOMPILE Clause with the EXECUTE Statement 339
RECOMPILE Hints to Control Individual Statements 339
Avoiding Recompilations 340
Don’t Interleave DDL and DML Statements 340
Avoiding Recompilations Caused by Statistics Change 342
Using the KEEPFIXED PLAN Option 342
Disable Auto Update Statistics on the Table 344
Using Table Variables 344
Avoiding Changing SET Options Within a Stored Procedure 346
Using OPTIMIZE FOR Query Hint 347
Using Plan Guides 349
Summary 354
Chapter 18: Query Design Analysis 355
Query Design Recommendations 355
Operating on Small Result Sets 356
Limit the Number of Columns in select_list 356
Use Highly Selective WHERE Clauses 357
Using Indexes Effectively 357
Avoid Nonsargable Search Conditions 358
BETWEEN vs. IN/OR 358
LIKE Condition 360
!< Condition vs. >
Avoid Arithmetic Operators on the WHERE Clause Column 362
Avoid Functions on the WHERE Clause Column 364
SUBSTRING vs. LIKE 364
Date Part Comparison 365
Avoiding Optimizer Hints 366
JOIN Hint 367
INDEX Hints 370
Using Domain and Referential Integrity 372
NOT NULL Constraint 372
Declarative Referential Integrity 375
Summary 377
Chapter 19: Reduce Query Resource Use 378
Avoiding Resource-Intensive Queries 378
Avoid Data Type Conversion 378
Use EXISTS over COUNT(*) to Verify Data Existence 381
Use UNION ALL Instead of UNION 382
Use Indexes for Aggregate and Sort Conditions 383
Avoid Local Variables in a Batch Query 384
Be Careful When Naming Stored Procedures 388
Reducing the Number of Network Round-Trips 390
Execute Multiple Queries Together 390
Use SET NOCOUNT 391
Reducing the Transaction Cost 391
Reduce Logging Overhead 391
Reduce Lock Overhead 393
Summary 394
Chapter 20: Blocking and Blocked Processes 395
Blocking Fundamentals 395
Understanding Blocking 396
Atomicity 396
SET XACT_ABORT ON 398
Explicit Rollback 398
Consistency 399
Isolation 399
Durability 400
Locks 401
Lock Granularity 401
Row-Level Lock 402
Key-Level Lock 403
Page-Level Lock 404
Extent-Level Lock 404
Heap or B-tree Lock 405
Table-Level Lock 405
Database-Level Lock 405
Lock Operations and Modes 406
Lock Escalation 406
Lock Modes 406
Shared (S) Mode 407
Update (U) Mode 407
Exclusive (X) Mode 411
Intent Shared (IS), Intent Exclusive (IX and Shared with Intent Exclusive (SIX) Modes 411
Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes 412
Bulk Update (BU) Mode 412
Key-range Mode 413
Lock Compatibility 413
Isolation Levels 413
Read Uncommitted 414
Read Committed 414
Repeatable Read 415
Serializable 418
Snapshot 423
Effect of Indexes on Locking 423
Effect of a Nonclustered Index 424
Effect of a Clustered Index 426
Effect of Indexes on the Serializable Isolation Level 427
Capturing Blocking Information 427
Capturing Blocking Information with SQL 428
Extended Events and the blocked_process_report Event 430
Blocking Resolutions 432
Optimize the Queries 433
Decrease the Isolation Level 433
Partition the Contended Data 434
Recommendations to Reduce Blocking 435
Automation to Detect and Collect Blocking Information 436
Summary 439
Chapter 21: Causes and Solutions for Deadlocks 440
Deadlock Fundamentals 440
Choosing the Deadlock Victim 441
Using Error Handling to Catch a Deadlock 442
Deadlock Analysis 443
Collecting Deadlock Information 443
Analyzing the Deadlock 445
Avoiding Deadlocks 452
Accessing Resources in the Same Physical Order 452
Decreasing the Number of Resources Accessed 453
Convert a Nonclustered Index to a Clustered Index 453
Use a Covering Index for a SELECT Statement 453
Minimizing Lock Contention 453
Implement Row Versioning 454
Decrease the Isolation Level 454
Use Locking Hints 454
Summary 455
Chapter 22: Row-by-Row Processing 456
Cursor Fundamentals 456
Cursor Location 458
Client-Side Cursors 458
Server-Side Cursors 459
Cursor Concurrency 459
Read-Only 459
Optimistic 459
Cursor Types 460
Forward-Only Cursors 461
Static Cursors 461
Keyset-Driven Cursors 462
Dynamic Cursors 463
Cursor Cost Comparison 463
Cost Comparison on Cursor Location 463
Client-Side Cursors 463
Server-Side Cursors 464
Cost Comparison on Cursor Concurrency 465
Read-Only 465
Optimistic 465
Scroll Locks 466
Cost Comparison on Cursor Type 466
Forward-Only Cursors 467
Fast-Forward-Only Cursor 467
Static Cursors 467
Keyset-Driven Cursors 468
Dynamic Cursor 468
Default Result Set 469
Benefits 469
Multiple Active Result Sets 469
Drawbacks 470
Cursor Overhead 472
Analyzing Overhead with T-SQL Cursors 473
Cursor Recommendations 477
Summary 478
Chapter 23: Memory-Optimized OLTP Tables and Procedures 479
In-Memory OLTP Fundamentals 479
System Requirements 480
Basic Setup 480
Create Tables 481
In-Memory Indexes 486
Hash Index 486
Nonclustered Indexes 488
Index Maintenance 490
Natively Compiled Stored Procedures 491
Recommendations 493
Baselines 494
Correct Workload 494
Memory Optimization Advisor 494
Native Compilation Advisor 498
Summary 500
Chapter 24: Database Performance Testing 501
Database Performance Testing 501
A Repeatable Process 502
Distributed Replay 502
Capturing Data with the Server-Side Trace 503
Distributed Replay for Database Testing 507
Configuring the Client 508
Running the Distributed Tests 508
Conclusion 509
Chapter 25: Database Workload Optimization 510
Workload Optimization Fundamentals 510
Workload Optimization Steps 511
Sample Workload 512
Capturing the Workload 515
Analyzing the Workload 515
Identifying the Costliest Query 517
Determining the Baseline Resource Use of the Costliest Query 518
Overall Resource Use 519
Detailed Resource Use 519
Analyzing and Optimizing External Factors 521
Analyzing the Connection Options Used by the Application 522
Analyzing the Effectiveness of Statistics 522
Analyzing the Need for Defragmentation 523
Analyzing the Internal Behavior of the Costliest Query 527
Analyzing the Query Execution Plan 528
Identifying the Costly Steps in the Execution Plan 529
Analyzing the Processing Strategy 529
Optimizing the Costliest Query 530
Modifying the Code 530
Fixing the Key Lookup Operation 533
Tuning the Second Query 534
Creating a Wrapper Procedure 536
Analyzing the Effect on Database Workload 537
Iterating Through Optimization Phases 538
Summary 541
Chapter 26: SQL Server Optimization Checklist 542
Database Design 542
Balancing Under- and Overnormalization 543
Benefiting from Entity-Integrity Constraints 544
Benefiting from Domain and Referential Integrity Constraints 546
Adopting Index-Design Best Practices 547
Avoiding the Use of the sp_ Prefix for Stored Procedure Names 549
Minimizing the Use of Triggers 549
Consider Putting Tables into In-Memory Storage 549
Configuration Settings 549
Memory Configuration Options 550
Cost Threshold for Parallelism 550
Max Degree of Parallelism 550
Optimize for Ad Hoc Workloads 550
Blocked Process Threshold 550
Database File Layout 551
Database Compression 551
Database Administration 551
Keep the Statistics Up-to-Date 552
Maintain a Minimum Amount of Index Defragmentation 552
Avoid Database Functions Such As AUTO_CLOSE or AUTO_ SHRINK 552
Database Backup 553
Incremental and Transaction Log Backup Frequency 553
Backup Scheduling Distribution 553
Backup Compression 554
Query Design 554
Use the Command SET NOCOUNT ON 555
Explicitly Define the Owner of an Object 555
Avoid Nonsargable Search Conditions 555
Avoid Arithmetic Expressions on the WHERE Clause Column 556
Avoid Optimizer Hints 556
Stay Away from Nesting Views 557
Ensure No Implicit Data Type Conversions 557
Minimize Logging Overhead 557
Adopt Best Practices for Reusing Execution Plans 557
Caching Execution Plans Effectively 558
Minimizing Recompilation of Execution Plans 558
Adopt Best Practices for Database Transactions 558
Eliminate or Reduce the Overhead of Database Cursors 559
Natively Compile Stored Procedures 559
Summary 559
Index 560