SQL Server 2017 Query Performance Tuning: troubleshoot and optimize query performance

Table of Contents......Page 5
About the Author......Page 24
About the Technical Reviewer......Page 25
Acknowledgments......Page 27
Introduction......Page 28
Chapter 1: SQL Query Performance Tuning......Page 29
  The Core Process......Page 31
  Iterating the Process......Page 35
  Performance Targets......Page 38
 Performance Baseline......Page 39
 Where to Focus Efforts......Page 41
 SQL Server Performance Killers......Page 43
  Insufficient or Inaccurate Indexing......Page 44
  Poorly Generated Execution Plans......Page 45
  Excessive Blocking and Deadlocks......Page 46
  Inappropriate Database Design......Page 47
  Frequent Recompilation of Queries......Page 48
 Summary......Page 49
Chapter 2: Memory Performance Analysis......Page 51
 Performance Monitor Tool......Page 52
 Dynamic Management Views......Page 54
  Identifying Bottlenecks......Page 56
  Bottleneck Resolution......Page 57
  SQL Server Memory Management......Page 58
  Pages/Sec and Page Faults/Sec......Page 64
  Paging File %Usage and Page File %Usage......Page 65
  Page Life Expectancy......Page 66
  Memory Grants Pending......Page 67
 Additional Memory Monitoring Tools......Page 68
  DBCC MEMORYSTATUS......Page 69
   Sys.dm_os_memory_brokers......Page 70
   Sys.dm_os_ring_buffers......Page 71
   Sys.dm_xtp_system_memory_consumers......Page 72
 Memory Bottleneck Resolutions......Page 73
  Allocating More Memory to SQL Server......Page 75
  Changing from a 32-Bit to a 64-Bit Processor......Page 76
  Enabling 3GB of Process Address Space......Page 77
 Summary......Page 78
 Disk Bottleneck Analysis......Page 79
  Disk Counters......Page 80
  Disk Bytes/Sec......Page 82
  Sys.dm_io_virtual_file_stats......Page 83
  Sys.dm_os_wait_stats......Page 84
  Optimizing Application Workload......Page 85
  Using a RAID Array......Page 86
   RAID 5......Page 87
   RAID 6......Page 88
  Using a SAN System......Page 89
  Adding System Memory......Page 90
  Creating Multiple Files and Filegroups......Page 91
  Moving the Log Files to a Separate Physical Disk......Page 94
  Using Partitioned Tables......Page 95
 Summary......Page 96
 Processor Bottleneck Analysis......Page 97
  % Processor Time......Page 99
  Context Switches/Sec......Page 100
  SQL Recompilations/Sec......Page 101
  Query Store......Page 102
  Optimizing Application Workload......Page 103
  Not Running Unnecessary Software......Page 104
  Bytes Total/Sec......Page 105
 Network Bottleneck Resolutions......Page 106
 SQL Server Overall Performance......Page 107
   Full Scans/Sec......Page 108
   Dynamic Management Objects......Page 109
   Lock Timeouts/Sec and Lock Wait Time (Ms)......Page 110
  Nonreusable Execution Plans......Page 111
  Batch Requests/Sec......Page 112
 Summary......Page 113
 Considerations for Monitoring Virtual and Hosted Machines......Page 114
  Creating a Reusable List of Performance Counters......Page 116
  Creating a Counter Log Using the List of Performance Counters......Page 119
   Prefer Counter Logs......Page 124
   Increase the Sampling Interval......Page 125
  System Behavior Analysis Against Baseline......Page 126
 Summary......Page 129
 Extended Events......Page 130
  Extended Events Sessions......Page 131
  Global Fields......Page 138
  Event Filters......Page 140
  Event Fields......Page 143
  Data Storage......Page 144
  Finishing the Session......Page 146
  The Built-in system_health Session......Page 148
  Creating a Session Script Using the GUI......Page 150
  Defining a Session Using T-SQL......Page 151
 Using Causality Tracking......Page 153
  Set Max File Size Appropriately......Page 154
 Other Methods for Query Performance Metrics......Page 155
 Summary......Page 157
 Costly Queries......Page 158
  Identifying Costly Queries......Page 160
  Costly Queries with a Single Execution......Page 162
  Costly Queries with Multiple Executions......Page 164
  Identifying Slow-Running Queries......Page 168
 Execution Plans......Page 169
  Analyzing a Query Execution Plan......Page 171
  Identifying the Costly Steps in an Execution Plan......Page 175
  Analyzing Index Effectiveness......Page 177
  Analyzing Join Effectiveness......Page 180
   Hash Join......Page 181
   Merge Join......Page 183
   Nested Loop Join......Page 184
   Adaptive Join......Page 186
  Actual vs. Estimated Execution Plans......Page 188
  Execution Plan Tooling......Page 191
   Find Node......Page 192
   Compare Plans......Page 193
   Scenarios......Page 196
   Live Execution Plans......Page 197
   Query Thread Profiles......Page 201
  Client Statistics......Page 203
  Execution Time......Page 205
  STATISTICS IO......Page 206
  Actual Execution Plans......Page 208
 Summary......Page 209
 What Is an Index?......Page 211
  The Benefit of Indexes......Page 214
  Index Overhead......Page 217
 Index Design Recommendations......Page 220
  Examine the WHERE Clause and JOIN Criteria Columns......Page 221
  Use Narrow Indexes......Page 223
  Examine Column Uniqueness......Page 226
  Examine the Column Data Type......Page 230
  Consider Index Column Order......Page 231
  Consider the Type of Index......Page 234
  Relationship with Nonclustered Indexes......Page 235
   Keep Clustered Indexes Narrow......Page 239
   Where Possible, Make the Clustered Index Unique......Page 242
   Accessing the Data Directly......Page 243
   Retrieving Presorted Data......Page 244
  Poor Design Practices for a Clustered Index......Page 245
   Frequently Updatable Columns......Page 246
  Nonclustered Index Maintenance......Page 247
  Defining the Lookup Operation......Page 248
   When to Use a Nonclustered Index......Page 249
  Benefits of a Clustered Index over a Nonclustered Index......Page 250
  Benefits of a Nonclustered Index over a Clustered Index......Page 253
 Summary......Page 256
 Advanced Indexing Techniques......Page 257
  Covering Indexes......Page 258
  A Pseudoclustered Index......Page 260
  Index Intersections......Page 261
  Index Joins......Page 264
  Filtered Indexes......Page 268
  Indexed Views......Page 272
   Overhead......Page 273
   Usage Scenarios......Page 274
  Index Compression......Page 279
 Columnstore Indexes......Page 282
  Columnstore Index Storage......Page 283
  Columnstore Index Behavior......Page 284
 Special Index Types......Page 292
  Spatial......Page 293
  Index on Computed Columns......Page 294
  CREATE INDEX Statement Processed As a Query......Page 295
  Online Index Creation......Page 296
 Summary......Page 297
 Database Engine Tuning Advisor Mechanisms......Page 298
  Tuning a Query......Page 304
  Tuning a Trace Workload......Page 310
  Tuning from the Procedure Cache......Page 313
  Tuning from the Query Store......Page 314
 Database Engine Tuning Advisor Limitations......Page 315
 Summary......Page 316
 Query Store Function and Design......Page 317
  Query Store Behavior......Page 318
   Query Information......Page 321
   Query Runtime Data......Page 325
  Controlling the Query Store......Page 330
 Query Store Reporting......Page 333
 Plan Forcing......Page 337
 Query Store for Upgrades......Page 340
 Summary......Page 341
 Purpose of Lookups......Page 343
 Drawbacks of Lookups......Page 346
 Analyzing the Cause of a Lookup......Page 347
  Using a Clustered Index......Page 350
  Using a Covering Index......Page 351
  Using an Index Join......Page 357
 Summary......Page 359
Chapter 13: Statistics, Data Distribution, and Cardinality......Page 361
  Statistics on an Indexed Column......Page 362
  Benefits of Updated Statistics......Page 363
  Drawbacks of Outdated Statistics......Page 369
 Statistics on a Nonindexed Column......Page 371
  Benefits of Statistics on a Nonindexed Column......Page 372
  Drawback of Missing Statistics on a Nonindexed Column......Page 379
 Analyzing Statistics......Page 383
  Density......Page 387
  Statistics on a Multicolumn Index......Page 388
  Statistics on a Filtered Index......Page 391
  Cardinality......Page 394
   Enabling and Disabling the Cardinality Estimator......Page 401
  Statistics DMOs......Page 403
 Statistics Maintenance......Page 404
  Auto Create Statistics......Page 405
  Auto Update Statistics......Page 406
  Manual Maintenance......Page 409
  Manage Statistics Settings......Page 411
  Generate Statistics......Page 412
  Status of Auto Create Statistics......Page 414
 Analyzing the Effectiveness of Statistics for a Query......Page 415
  Resolving a Missing Statistics Issue......Page 416
  Resolving an Outdated Statistics Issue......Page 420
  Backward Compatibility of Statistics......Page 424
  Automatic Update Statistics Asynchronously......Page 425
 Summary......Page 426
Chapter 14: Index Fragmentation......Page 427
 Discussion on Fragmentation......Page 428
  Data Modification and the Rowstore Indexes......Page 429
   Page Split by an UPDATE Statement......Page 432
   Page Split by an INSERT Statement......Page 437
  Data Modification and the Columnstore Indexes......Page 439
  Rowstore Overhead......Page 442
  Columnstore Overhead......Page 445
 Analyzing the Amount of Fragmentation......Page 447
 Analyzing the Fragmentation of a Small Table......Page 452
 Fragmentation Resolutions......Page 454
  Dropping and Re-creating the Index......Page 455
  Re-creating the Index with the DROP_EXISTING Clause......Page 456
  Executing the ALTER INDEX REBUILD Statement......Page 457
  Executing the ALTER INDEX REORGANIZE Statement......Page 461
  Defragmentation and Partitions......Page 468
 Significance of the Fill Factor......Page 469
 Automatic Maintenance......Page 473
 Summary......Page 474
 Execution Plan Generation......Page 475
  Binding......Page 478
  Optimization......Page 481
   Multiple Optimization Phases......Page 483
   Parallel Plan Optimization......Page 488
  Query Plan......Page 492
 Aging of the Execution Plan......Page 493
 Summary......Page 494
 Analyzing the Execution Plan Cache......Page 495
 Execution Plan Reuse......Page 497
 Ad Hoc Workload......Page 498
  Prepared Workload......Page 499
  Plan Reusability of an Ad Hoc Workload......Page 500
   Optimize for an Ad Hoc Workload......Page 503
   Simple Parameterization......Page 505
   Forced Parameterization......Page 509
  Plan Reusability of a Prepared Workload......Page 512
   Stored Procedures......Page 513
    Other Performance Benefits of Stored Procedures......Page 516
   sp_executesql......Page 519
 Query Plan Hash and Query Hash......Page 523
  Explicitly Parameterize Variable Parts of a Query......Page 528
  Code with sp_executesql to Avoid Stored Procedure Deployment......Page 529
  Prefer sp_executesql Over EXECUTE for Dynamic Queries......Page 530
  Do Not Allow Implicit Resolution of Objects in Queries......Page 532
 Summary......Page 533
 Parameter Sniffing......Page 534
 Bad Parameter Sniffing......Page 538
  Identifying Bad Parameter Sniffing......Page 541
  Mitigating Bad Parameter Sniffing......Page 544
 Summary......Page 551
 Benefits and Drawbacks of Recompilation......Page 552
 Identifying the Statement Causing Recompilation......Page 556
 Analyzing Causes of Recompilation......Page 558
  Statistics Changes......Page 559
  Deferred Object Resolution......Page 563
   Recompilation Because of a Regular Table......Page 564
   Recompilation Because of a Local Temporary Table......Page 565
  SET Options Changes......Page 567
  Explicit Call to sp_recompile......Page 568
   RECOMPILE Clause with the CREATE PROCEDURE Statement......Page 570
   RECOMPILE Clause with the EXECUTE Statement......Page 571
   RECOMPILE Hints to Control Individual Statements......Page 572
 Avoiding Recompilations......Page 573
  Don’t Interleave DDL and DML Statements......Page 574
  Avoiding Recompilations Caused by Statistics Change......Page 576
  Using the KEEPFIXED PLAN Option......Page 577
  Using Table Variables......Page 579
  Avoiding Changing SET Options Within a Stored Procedure......Page 581
  Using OPTIMIZE FOR Query Hint......Page 582
  Using Plan Guides......Page 584
 Summary......Page 590
 Query Design Recommendations......Page 592
 Operating on Small Result Sets......Page 593
  Limit the Number of Columns in select_list......Page 594
  Use Highly Selective WHERE Clauses......Page 595
  Avoid Nonsargable Search Conditions......Page 596
   BETWEEN vs. IN/OR......Page 598
   LIKE Condition......Page 601
   !< Condition vs. >= Condition......Page 602
  Avoid Arithmetic Operators on the WHERE Clause Column......Page 603
   SUBSTRING vs. LIKE......Page 605
   Date Part Comparison......Page 606
   Custom Scalar UDF......Page 609
 Minimize Optimizer Hints......Page 612
  JOIN Hint......Page 613
  INDEX Hints......Page 618
  NOT NULL Constraint......Page 620
  Declarative Referential Integrity......Page 623
 Summary......Page 628
 Avoiding Resource-Intensive Queries......Page 629
  Avoid Data Type Conversion......Page 630
  Use EXISTS over COUNT(*) to Verify Data Existence......Page 633
  Use UNION ALL Instead of UNION......Page 634
  Use Indexes for Aggregate and Sort Conditions......Page 636
  Be Cautious with Local Variables in a Batch Query......Page 638
  Be Careful When Naming Stored Procedures......Page 643
 Reducing the Number of Network Round-Trips......Page 645
 Reducing the Transaction Cost......Page 646
  Reduce Logging Overhead......Page 647
  Reduce Lock Overhead......Page 649
 Summary......Page 651
Chapter 21: Blocking and Blocked Processes......Page 652
 Blocking Fundamentals......Page 653
  Atomicity......Page 654
   SET XACT_ABORT ON......Page 656
   Explicit Rollback......Page 657
  Consistency......Page 658
  Isolation......Page 659
  Durability......Page 660
  Lock Granularity......Page 662
  Row-Level Lock......Page 663
  Key-Level Lock......Page 664
  Heap or B-tree Lock......Page 666
  Table-Level Lock......Page 667
  Lock Escalation......Page 668
  Lock Modes......Page 669
   Shared (S) Mode......Page 670
   Update (U) Mode......Page 671
  Exclusive (X) Mode......Page 677
  Intent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX) Modes......Page 678
  Bulk Update (BU) Mode......Page 679
 Isolation Levels......Page 680
  Read Uncommitted......Page 681
  Read Committed......Page 682
  Repeatable Read......Page 684
  Serializable......Page 688
  Snapshot......Page 695
 Effect of Indexes on Locking......Page 696
  Effect of a Nonclustered Index......Page 697
  Effect of a Clustered Index......Page 699
  Effect of Indexes on the Serializable Isolation Level......Page 700
 Capturing Blocking Information......Page 701
  Capturing Blocking Information with SQL......Page 702
  Extended Events and the blocked_process_report Event......Page 705
  Optimize the Queries......Page 709
 Partition the Contended Data......Page 711
 Recommendations to Reduce Blocking......Page 713
 Automation to Detect and Collect Blocking Information......Page 714
 Summary......Page 719
 Deadlock Fundamentals......Page 720
  Choosing the Deadlock Victim......Page 722
  Using Error Handling to Catch a Deadlock......Page 723
  Collecting Deadlock Information......Page 724
  Analyzing the Deadlock......Page 728
  Accessing Resources in the Same Physical Order......Page 736
   Convert a Nonclustered Index to a Clustered Index......Page 737
   Implement Row Versioning......Page 738
   Use Locking Hints......Page 739
 Summary......Page 740
 Cursor Fundamentals......Page 741
   Client-Side Cursors......Page 744
   Read-Only......Page 745
   Optimistic......Page 746
  Cursor Types......Page 747
   Forward-Only Cursors......Page 748
   Keyset-Driven Cursors......Page 749
   Dynamic Cursors......Page 750
  Cost Comparison on Cursor Location......Page 751
   Client-Side Cursors......Page 752
   Server-Side Cursors......Page 753
   Read-Only......Page 754
   Optimistic......Page 755
   Scroll Locks......Page 756
   Forward-Only Cursors......Page 757
   Static Cursors......Page 758
   Keyset-Driven Cursors......Page 759
   Dynamic Cursor......Page 760
 Default Result Set......Page 761
  Multiple Active Result Sets......Page 762
  Drawbacks......Page 763
  Analyzing Overhead with T-SQL Cursors......Page 766
  Cursor Recommendations......Page 771
 Summary......Page 772
Chapter 24: Memory-Optimized OLTP Tables and Procedures......Page 773
 In-Memory OLTP Fundamentals......Page 774
  System Requirements......Page 775
  Basic Setup......Page 776
  Create Tables......Page 777
  In-Memory Indexes......Page 783
   Hash Index......Page 784
   Nonclustered Indexes......Page 787
   Columnstore Index......Page 789
   Statistics Maintenance......Page 790
 Natively Compiled Stored Procedures......Page 791
  Memory Optimization Advisor......Page 794
  Native Compilation Advisor......Page 799
 Summary......Page 801
Chapter 25: Automated Tuning in Azure SQL Database and SQL Server......Page 802
 Automatic Plan Correction......Page 803
  Tuning Recommendations......Page 804
   Azure Portal......Page 811
   Automatic Tuning in Action......Page 815
 Azure SQL Database Automatic Index Management......Page 818
  Batch Mode Memory Grant Feedback......Page 829
  Interleaved Execution......Page 834
 Summary......Page 842
Chapter 26: Database Performance Testing......Page 843
 Database Performance Testing......Page 844
  A Repeatable Process......Page 845
  Distributed Replay......Page 846
 Capturing Data with the Server-Side Trace......Page 847
 Distributed Replay for Database Testing......Page 851
  Configuring the Client......Page 853
  Running the Distributed Tests......Page 854
 Conclusion......Page 855
Chapter 27: Database Workload Optimization......Page 856
  Workload Optimization Steps......Page 857
  Sample Workload......Page 859
 Capturing the Workload......Page 864
 Analyzing the Workload......Page 865
 Identifying the Costliest Query......Page 868
  Determining the Baseline Resource Use of the  Costliest Query......Page 869
  Overall Resource Use......Page 870
  Detailed Resource Use......Page 871
  Analyzing the Connection Options Used by the Application......Page 874
  Analyzing the Effectiveness of Statistics......Page 875
  Analyzing the Need for Defragmentation......Page 878
  Analyzing the Query Execution Plan......Page 881
  Identifying the Costly Steps in the Execution Plan......Page 883
 Optimizing the Costliest Query......Page 884
  Modifying the Code......Page 885
  Fixing the Key Lookup Operation......Page 888
  Tuning the Second Query......Page 889
  Creating a Wrapper Procedure......Page 891
 Analyzing the Effect on Database Workload......Page 894
 Iterating Through Optimization Phases......Page 895
 Summary......Page 898
Chapter 28: SQL Server Optimization Checklist......Page 899
  Use Entity-Integrity Constraints......Page 900
  Maintain Domain and Referential Integrity Constraints......Page 903
  Adopt Index-Design Best Practices......Page 906
  Put Tables into In-Memory Storage......Page 908
 Configuration Settings......Page 909
  Max Degree of Parallelism......Page 910
  Database File Layout......Page 911
 Database Administration......Page 912
  Keep the Statistics Up-to-Date......Page 913
  Avoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINK......Page 914
  Incremental and Transaction Log Backup Frequency......Page 915
  Backup Scheduling Distribution......Page 916
 Query Design......Page 917
  Avoid Nonsargable Search Conditions......Page 918
  Avoid Arithmetic Expressions on the WHERE Clause Column......Page 919
  Avoid Optimizer Hints......Page 920
  Minimize Logging Overhead......Page 921
   Caching Execution Plans Effectively......Page 922
  Adopt Best Practices for Database Transactions......Page 923
  Eliminate or Reduce the Overhead of Database Cursors......Page 924
 Summary......Page 925
Index......Page 926