(This page has no text content)
SQL Server 2022 Query Performance Tuning Troubleshoot and Optimize Query Performance Sixth Edition Grant Fritchey
SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance ISBN-13 (pbk): 978-1-4842-8890-0 ISBN-13 (electronic): 978-1-4842-8891-7 https://doi.org/10.1007/978-1-4842-8891-7 Copyright © 2022 by Grant Fritchey This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover Photo by Lucas Kapla on Unsplash Distributed to the book trade worldwide by Springer Science+Business Media LLC, 1 New York Plaza, Suite 4600, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm. com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub. For more detailed information, please visit http://www.apress.com/source-code. Printed on acid-free paper Grant Fritchey Grafton, MA, USA
v Table of Contents About the Author ����������������������������������������������������������������������������������������������������xix About the Technical Reviewer ��������������������������������������������������������������������������������xxi Acknowledgments ������������������������������������������������������������������������������������������������xxiii Introduction �����������������������������������������������������������������������������������������������������������xxv Chapter 1: Query Performance Tuning ���������������������������������������������������������������������� 1 The Query Performance Tuning Process ��������������������������������������������������������������������������������������� 3 Performance Issues ����������������������������������������������������������������������������������������������������������������� 4 A Repetitive Process ���������������������������������������������������������������������������������������������������������������� 7 Understanding What Defines “Good Enough” ����������������������������������������������������������������������������� 11 Establishing Comparison Points �������������������������������������������������������������������������������������������������� 12 Most Likely Performance Issues ������������������������������������������������������������������������������������������������� 13 Common Performance Issues ����������������������������������������������������������������������������������������������������� 15 Insufficient or Poor Indexes ��������������������������������������������������������������������������������������������������� 15 Inaccurate or Missing Statistics �������������������������������������������������������������������������������������������� 16 Bad T-SQL ������������������������������������������������������������������������������������������������������������������������������ 17 Problematic Execution Plans ������������������������������������������������������������������������������������������������� 17 Excessive Blocking ���������������������������������������������������������������������������������������������������������������� 18 Deadlocks ������������������������������������������������������������������������������������������������������������������������������ 18 Non-Set-Based Operations ���������������������������������������������������������������������������������������������������� 19 Incorrect Database Design ���������������������������������������������������������������������������������������������������� 19 Poor Execution Plan Reuse ���������������������������������������������������������������������������������������������������� 20 Frequent Recompilation of Queries ��������������������������������������������������������������������������������������� 20 Summary������������������������������������������������������������������������������������������������������������������������������������� 20
vi Chapter 2: Execution Plan Generation and the Query Optimizer ���������������������������� 23 The Query Optimization Process ������������������������������������������������������������������������������������������������� 23 Optimization Preparation ������������������������������������������������������������������������������������������������������� 24 Optimization �������������������������������������������������������������������������������������������������������������������������������� 29 Simplification ������������������������������������������������������������������������������������������������������������������������� 31 Trivial Plan Match ������������������������������������������������������������������������������������������������������������������ 31 Optimization Phases �������������������������������������������������������������������������������������������������������������� 31 Generating Parallel Execution Plans ������������������������������������������������������������������������������������������� 36 Execution Plan Caching ��������������������������������������������������������������������������������������������������������������� 38 Aging of the Execution Plan ��������������������������������������������������������������������������������������������������� 39 Summary������������������������������������������������������������������������������������������������������������������������������������� 40 Chapter 3: Methods for Capturing Query Performance Metrics ����������������������������� 41 Methods to Capture Query Performance Metrics ������������������������������������������������������������������������ 42 Include Client Statistics ��������������������������������������������������������������������������������������������������������� 42 Connection Properties ����������������������������������������������������������������������������������������������������������� 42 SET STATISTICS TIME/IO �������������������������������������������������������������������������������������������������������� 43 QueryTimeStats in the Execution Plan����������������������������������������������������������������������������������� 43 Trace Events (Profiler) ����������������������������������������������������������������������������������������������������������� 44 Dynamic Management Views ������������������������������������������������������������������������������������������������������ 45 Actively Executing Queries ���������������������������������������������������������������������������������������������������� 45 Previously Executed Queries ������������������������������������������������������������������������������������������������� 47 Query Store ��������������������������������������������������������������������������������������������������������������������������������� 48 Extended Events ������������������������������������������������������������������������������������������������������������������������� 48 Creating an Extended Events Session ����������������������������������������������������������������������������������� 50 Adding and Configuring Events ��������������������������������������������������������������������������������������������� 52 Defining Targets ��������������������������������������������������������������������������������������������������������������������� 63 Working with Sessions ���������������������������������������������������������������������������������������������������������� 67 Adding Causality Tracking ����������������������������������������������������������������������������������������������������� 68 Scripting Extended Events ����������������������������������������������������������������������������������������������������� 68 Table of ConTenTs
vii Live Data Explorer Window ���������������������������������������������������������������������������������������������������� 70 General Recommendations for Using Extended Events ��������������������������������������������������������� 79 Summary������������������������������������������������������������������������������������������������������������������������������������� 80 Chapter 4: Analyzing Query Behavior Using Execution Plans ��������������������������������� 81 Estimated vs� Actual Execution Plans ����������������������������������������������������������������������������������������� 82 Capturing Execution Plans ���������������������������������������������������������������������������������������������������������� 83 SQL Server Management Studio ������������������������������������������������������������������������������������������� 83 Dynamic Management Views ������������������������������������������������������������������������������������������������ 85 Query Store ���������������������������������������������������������������������������������������������������������������������������� 88 Extended Events �������������������������������������������������������������������������������������������������������������������� 89 What Is Inside an Execution Plan ������������������������������������������������������������������������������������������������ 91 Reading an Execution Plan ���������������������������������������������������������������������������������������������������� 97 What Do You Look for in an Execution Plan? ������������������������������������������������������������������������������� 98 First Operator ������������������������������������������������������������������������������������������������������������������������ 99 Warnings ����������������������������������������������������������������������������������������������������������������������������� 101 Most Costly Operations �������������������������������������������������������������������������������������������������������� 103 Fat Pipes ������������������������������������������������������������������������������������������������������������������������������ 104 Extra Operators �������������������������������������������������������������������������������������������������������������������� 104 Scans ����������������������������������������������������������������������������������������������������������������������������������� 106 Estimate vs� Actual �������������������������������������������������������������������������������������������������������������� 106 After the Guideposts ������������������������������������������������������������������������������������������������������������ 108 Tools That Assist You with Execution Plans ������������������������������������������������������������������������������� 108 SQL Server Management Studio ����������������������������������������������������������������������������������������� 109 Third Party ��������������������������������������������������������������������������������������������������������������������������� 119 Summary����������������������������������������������������������������������������������������������������������������������������������� 120 Chapter 5: Statistics, Data Distribution, and Cardinality �������������������������������������� 121 Statistics in the Query Optimization Process ���������������������������������������������������������������������������� 122 Statistics on Rowstore Indexed Columns ���������������������������������������������������������������������������� 122 Statistics on Nonindexed Columns �������������������������������������������������������������������������������������� 130 Analyzing Statistics ������������������������������������������������������������������������������������������������������������������� 138 Header ��������������������������������������������������������������������������������������������������������������������������������� 140 Table of ConTenTs
viii Density ��������������������������������������������������������������������������������������������������������������������������������� 140 Histogram ���������������������������������������������������������������������������������������������������������������������������� 141 Cardinality ��������������������������������������������������������������������������������������������������������������������������� 143 Statistics on a Multicolumn Index ��������������������������������������������������������������������������������������� 146 Statistics on a Filtered Index ����������������������������������������������������������������������������������������������� 149 Controlling the Cardinality Estimator ����������������������������������������������������������������������������������� 151 Statistics Maintenance ������������������������������������������������������������������������������������������������������������� 153 Auto Create Statistics ���������������������������������������������������������������������������������������������������������� 153 Auto Update Statistics ��������������������������������������������������������������������������������������������������������� 154 Auto Update Statistics Asynchronously ������������������������������������������������������������������������������� 154 Manual Maintenance ����������������������������������������������������������������������������������������������������������� 155 Manage Statistics Settings �������������������������������������������������������������������������������������������������� 155 Create Statistics Manually ��������������������������������������������������������������������������������������������������� 157 Analyzing the Effectiveness of Statistics for a Query ��������������������������������������������������������������� 159 Resolving a Missing Statistics Issue ����������������������������������������������������������������������������������� 160 Resolving an Outdated Statistics Issue ������������������������������������������������������������������������������� 164 Recommendations on Statistics ����������������������������������������������������������������������������������������������� 167 Backward Compatibility of Statistics ����������������������������������������������������������������������������������� 167 Auto Create Statistics ���������������������������������������������������������������������������������������������������������� 167 Auto Update Statistics ��������������������������������������������������������������������������������������������������������� 167 Automatic Update Statistics Asynchronously ���������������������������������������������������������������������� 168 Amount of Sampling to Collect Statistics ���������������������������������������������������������������������������� 168 Summary����������������������������������������������������������������������������������������������������������������������������������� 169 Chapter 6: Using Query Store for Query Performance and Execution Plans ��������� 171 Query Store Function and Design ��������������������������������������������������������������������������������������������� 171 Information Collected by Query Store ���������������������������������������������������������������������������������� 173 Query Runtime Data ������������������������������������������������������������������������������������������������������������ 177 Controlling Query Store ������������������������������������������������������������������������������������������������������������� 179 Capture Mode ���������������������������������������������������������������������������������������������������������������������� 181 Query Store Reporting ��������������������������������������������������������������������������������������������������������������� 183 Plan Forcing ������������������������������������������������������������������������������������������������������������������������������ 189 Table of ConTenTs
ix Forcing Query Hints ������������������������������������������������������������������������������������������������������������� 190 Optimized Plan Forcing�������������������������������������������������������������������������������������������������������� 192 Query Store for Upgrades ��������������������������������������������������������������������������������������������������������� 193 Summary����������������������������������������������������������������������������������������������������������������������������������� 194 Chapter 7: Execution Plan Cache Behavior ����������������������������������������������������������� 195 Querying the Plan Cache ����������������������������������������������������������������������������������������������������������� 195 Execution Plan Caching and Plan Reuse ����������������������������������������������������������������������������������� 197 Ad Hoc Workload ����������������������������������������������������������������������������������������������������������������� 198 Prepared Workload �������������������������������������������������������������������������������������������������������������� 209 sp_executesql ��������������������������������������������������������������������������������������������������������������������� 214 Prepare/Execute Model ������������������������������������������������������������������������������������������������������� 217 Query Hash and Query Plan Hash ��������������������������������������������������������������������������������������������� 217 Execution Plan Cache Recommendations ��������������������������������������������������������������������������������� 221 Explicitly Parameterize Values in Your Query ���������������������������������������������������������������������� 222 Use Stored Procedures Where You Can ������������������������������������������������������������������������������� 222 Use sp_executesql As an Alternative to Stored Procedures ������������������������������������������������ 223 Take Advantage of the Prepare/Execute Model ������������������������������������������������������������������� 223 Avoid Ad Hoc Queries ���������������������������������������������������������������������������������������������������������� 223 Enable Optimize For Ad Hoc ������������������������������������������������������������������������������������������������ 223 Summary����������������������������������������������������������������������������������������������������������������������������������� 224 Chapter 8: Query Recompilation ��������������������������������������������������������������������������� 225 Benefits and Drawbacks of Recompilation ������������������������������������������������������������������������������� 225 Identifying the Statement Being Recompiled ���������������������������������������������������������������������������� 229 Analyzing Causes of Recompilation ������������������������������������������������������������������������������������������ 231 Deferred Object Resolution �������������������������������������������������������������������������������������������������� 233 Avoiding Recompiles ����������������������������������������������������������������������������������������������������������������� 235 Avoid Interleaving DDL and DML Statements ���������������������������������������������������������������������� 236 Reduce Recompilation Caused by Statistics Changes��������������������������������������������������������� 238 Use Table Variables �������������������������������������������������������������������������������������������������������������� 242 Use Temporary Tables Across Multiple Scopes�������������������������������������������������������������������� 244 Avoid Changing SET Options Within a Batch ����������������������������������������������������������������������� 245 Table of ConTenTs
x Controlling Recompile Results �������������������������������������������������������������������������������������������������� 247 Plan Forcing ������������������������������������������������������������������������������������������������������������������������� 247 Query Hints �������������������������������������������������������������������������������������������������������������������������� 247 Plan Guides �������������������������������������������������������������������������������������������������������������������������� 250 Hint Forcing ������������������������������������������������������������������������������������������������������������������������� 254 Summary����������������������������������������������������������������������������������������������������������������������������������� 255 Chapter 9: Index Architecture������������������������������������������������������������������������������� 257 What Is a Rowstore Index?�������������������������������������������������������������������������������������������������������� 257 The Benefits of Indexes ������������������������������������������������������������������������������������������������������� 261 Index Overhead �������������������������������������������������������������������������������������������������������������������� 263 What Is a Columnstore Index? �������������������������������������������������������������������������������������������������� 266 Columnstore Index Storage ������������������������������������������������������������������������������������������������� 267 Index Design Recommendations ����������������������������������������������������������������������������������������������� 268 Type of Query Processing Being Performed ������������������������������������������������������������������������ 268 Determine Filtering Criteria ������������������������������������������������������������������������������������������������� 269 Use Narrow Indexes ������������������������������������������������������������������������������������������������������������ 271 Consider Selectivity of the Data ������������������������������������������������������������������������������������������ 274 Determine Data Type ����������������������������������������������������������������������������������������������������������� 277 Consider Column Order ������������������������������������������������������������������������������������������������������� 278 Determine Data Storage ������������������������������������������������������������������������������������������������������ 281 Rowstore Index Behavior ���������������������������������������������������������������������������������������������������������� 281 Clustered Indexes ���������������������������������������������������������������������������������������������������������������� 282 Nonclustered Indexes ���������������������������������������������������������������������������������������������������������� 293 Columnstore Index Behavior ����������������������������������������������������������������������������������������������������� 295 Columnstore Recommendations ����������������������������������������������������������������������������������������� 302 Summary����������������������������������������������������������������������������������������������������������������������������������� 303 Chapter 10: Index Behaviors �������������������������������������������������������������������������������� 305 Covering Indexes ����������������������������������������������������������������������������������������������������������������������� 305 A Pseudoclustered Index ����������������������������������������������������������������������������������������������������� 308 Recommendations ��������������������������������������������������������������������������������������������������������������� 308 Index Intersection ��������������������������������������������������������������������������������������������������������������������� 308 Table of ConTenTs
xi Index Joins �������������������������������������������������������������������������������������������������������������������������������� 312 Filtered Indexes������������������������������������������������������������������������������������������������������������������������� 313 Indexed Views ��������������������������������������������������������������������������������������������������������������������������� 317 Benefit ��������������������������������������������������������������������������������������������������������������������������������� 318 Overhead ����������������������������������������������������������������������������������������������������������������������������� 318 Usage Scenarios ������������������������������������������������������������������������������������������������������������������ 319 Index Compression�������������������������������������������������������������������������������������������������������������������� 323 Index Characteristics ���������������������������������������������������������������������������������������������������������������� 325 Different Column Sort Order ������������������������������������������������������������������������������������������������ 325 Index on Computed Columns ����������������������������������������������������������������������������������������������� 326 CREATE INDEX Statement Processed As a Query ���������������������������������������������������������������� 326 Parallel Index Creation ��������������������������������������������������������������������������������������������������������� 327 Online Index Creation ���������������������������������������������������������������������������������������������������������� 327 Considering the Database Engine Tuning Advisor ��������������������������������������������������������������� 327 OPTIMIZE_FOR_SEQUENTIAL_KEY �������������������������������������������������������������������������������������� 328 Resumable Indexes and Constraints ����������������������������������������������������������������������������������� 328 Special Index Types ������������������������������������������������������������������������������������������������������������������� 329 Full-Text ������������������������������������������������������������������������������������������������������������������������������� 330 Spatial ��������������������������������������������������������������������������������������������������������������������������������� 330 XML�������������������������������������������������������������������������������������������������������������������������������������� 331 Summary����������������������������������������������������������������������������������������������������������������������������������� 331 Chapter 11: Key Lookups and Solutions ��������������������������������������������������������������� 333 Purpose of Lookups ������������������������������������������������������������������������������������������������������������������ 333 Performance Issues Caused by Lookups ���������������������������������������������������������������������������������� 336 Analysis of the Causes of Lookups ������������������������������������������������������������������������������������������� 337 Techniques to Resolve Lookups ������������������������������������������������������������������������������������������������ 340 Create a Clustered Index ����������������������������������������������������������������������������������������������������� 340 Use a Covering Index ����������������������������������������������������������������������������������������������������������� 340 Take Advantage of Index Joins �������������������������������������������������������������������������������������������� 344 Summary����������������������������������������������������������������������������������������������������������������������������������� 346 Table of ConTenTs
xii Chapter 12: Dealing with Index Fragmentation ���������������������������������������������������� 347 Causes of Rowstore Fragmentation ������������������������������������������������������������������������������������������ 348 How Fragmentation Occurs in Rowstore Indexes ���������������������������������������������������������������� 348 How Fragmentation Occurs in the Columnstore Indexes ���������������������������������������������������� 353 Fragmentation Overhead ���������������������������������������������������������������������������������������������������������� 356 Rowstore Overhead ������������������������������������������������������������������������������������������������������������� 356 Columnstore Overhead �������������������������������������������������������������������������������������������������������� 360 Analyzing the Amount of Fragmentation ����������������������������������������������������������������������������������� 361 Analyzing the Fragmentation of a Small Table �������������������������������������������������������������������������� 364 Fragmentation Resolutions ������������������������������������������������������������������������������������������������������� 365 Drop and Recreate the Index ����������������������������������������������������������������������������������������������� 365 Recreating the Index with the DROP_EXISTING Clause ������������������������������������������������������� 366 Execute the ALTER INDEX REBUILD Command �������������������������������������������������������������������� 367 Execute the ALTER INDEX REORGANIZE Command �������������������������������������������������������������� 368 Defragmentation and Partitions ������������������������������������������������������������������������������������������� 374 Significance of the Fill Factor ��������������������������������������������������������������������������������������������������� 376 Automatic Maintenance ������������������������������������������������������������������������������������������������������������ 380 Summary����������������������������������������������������������������������������������������������������������������������������������� 380 Chapter 13: Parameter-Sensitive Queries: Causes and Solutions ������������������������ 383 How Does Parameter Sniffing Work? ���������������������������������������������������������������������������������������� 383 Identifying Queries That Are Sensitive to Parameter Values ����������������������������������������������������� 389 Mechanisms for Addressing Plan-Sensitive Queries ���������������������������������������������������������������� 394 Disable Parameter Sniffing �������������������������������������������������������������������������������������������������� 394 Local Variables �������������������������������������������������������������������������������������������������������������������� 396 Recompile ���������������������������������������������������������������������������������������������������������������������������� 396 OPTIMIZE FOR Query Hint ���������������������������������������������������������������������������������������������������� 397 Force Plan ���������������������������������������������������������������������������������������������������������������������������� 400 Parameter Sensitive Plan Optimization ������������������������������������������������������������������������������� 400 Summary����������������������������������������������������������������������������������������������������������������������������������� 407 Table of ConTenTs
xiii Chapter 14: Query Design Analysis ���������������������������������������������������������������������� 409 Query Design Recommendations ���������������������������������������������������������������������������������������������� 409 Keep Your Result Sets Small ����������������������������������������������������������������������������������������������������� 410 Limit the Columns in Your SELECT List �������������������������������������������������������������������������������� 410 Filter Your Data Through a WHERE Clause ��������������������������������������������������������������������������� 412 Use Indexes Effectively ������������������������������������������������������������������������������������������������������������� 413 Use Effective Search Conditions ������������������������������������������������������������������������������������������ 413 Avoid Operations on Columns ���������������������������������������������������������������������������������������������� 421 Custom Scalar UDF �������������������������������������������������������������������������������������������������������������� 426 Minimize Optimizer Hints ���������������������������������������������������������������������������������������������������������� 430 JOIN Hint ������������������������������������������������������������������������������������������������������������������������������ 431 INDEX Hints �������������������������������������������������������������������������������������������������������������������������� 436 Using Domain and Referential Integrity ������������������������������������������������������������������������������������ 437 NOT NULL Constraint ����������������������������������������������������������������������������������������������������������� 437 User-Defined Constraints ����������������������������������������������������������������������������������������������������� 440 Declarative Referential Integrity ������������������������������������������������������������������������������������������ 442 Summary����������������������������������������������������������������������������������������������������������������������������������� 448 Chapter 15: Reduce Query Resource Use ������������������������������������������������������������� 449 Avoiding Resource-Intensive Queries ��������������������������������������������������������������������������������������� 449 Use Appropriate Data Types ������������������������������������������������������������������������������������������������� 450 Test EXISTS over COUNT(*) to Verify Data Existence ����������������������������������������������������������� 453 Favor UNION ALL Over UNION ���������������������������������������������������������������������������������������������� 455 Ensure Indexes Are Used for Aggregate and Sort Operations���������������������������������������������� 458 Be Cautious with Local Variables in a Batch Query ������������������������������������������������������������� 459 Stored Procedure Names Actually Matter ��������������������������������������������������������������������������� 463 Reducing Network Overhead Where Possible ��������������������������������������������������������������������������� 463 Execute Multiple Queries in Sets ����������������������������������������������������������������������������������������� 464 Use SET NOCOUNT ��������������������������������������������������������������������������������������������������������������� 464 Table of ConTenTs
xiv Techniques to Reduce Transaction Cost of a Query ������������������������������������������������������������������ 464 Reduce Logging Overhead ��������������������������������������������������������������������������������������������������� 465 Reduce Lock Overhead �������������������������������������������������������������������������������������������������������� 467 Summary����������������������������������������������������������������������������������������������������������������������������������� 470 Chapter 16: Blocking and Blocked Processes ������������������������������������������������������ 471 Blocking Fundamentals ������������������������������������������������������������������������������������������������������������ 471 A Short Discussion of Terminology �������������������������������������������������������������������������������������� 472 Introducing Blocking ������������������������������������������������������������������������������������������������������������ 473 Transactions and ACID Properties ��������������������������������������������������������������������������������������������� 473 Atomicity ����������������������������������������������������������������������������������������������������������������������������� 474 Consistency ������������������������������������������������������������������������������������������������������������������������� 478 Isolation ������������������������������������������������������������������������������������������������������������������������������� 478 Durability ����������������������������������������������������������������������������������������������������������������������������� 479 Lock Types �������������������������������������������������������������������������������������������������������������������������������� 479 Row Locks ��������������������������������������������������������������������������������������������������������������������������� 480 Key Locks ���������������������������������������������������������������������������������������������������������������������������� 482 Page Locks �������������������������������������������������������������������������������������������������������������������������� 483 Extent Locks ������������������������������������������������������������������������������������������������������������������������ 483 Heap or B-Tree Locks ���������������������������������������������������������������������������������������������������������� 484 Rowgroup Locks ������������������������������������������������������������������������������������������������������������������ 484 Table Locks �������������������������������������������������������������������������������������������������������������������������� 484 Database Locks ������������������������������������������������������������������������������������������������������������������� 484 Lock Operations and Modes ������������������������������������������������������������������������������������������������������ 485 Lock Escalation ������������������������������������������������������������������������������������������������������������������� 485 Lock Modes ������������������������������������������������������������������������������������������������������������������������� 486 Lock Compatibility ��������������������������������������������������������������������������������������������������������������� 494 Isolation Levels ������������������������������������������������������������������������������������������������������������������������� 495 Read Uncommitted �������������������������������������������������������������������������������������������������������������� 495 Read Committed ������������������������������������������������������������������������������������������������������������������ 497 Repeatable Read ����������������������������������������������������������������������������������������������������������������� 499 Table of ConTenTs
xv Serializable �������������������������������������������������������������������������������������������������������������������������� 501 Snapshot ����������������������������������������������������������������������������������������������������������������������������� 503 Effect of Indexes on Locking ����������������������������������������������������������������������������������������������������� 503 Effect of a Nonclustered Index �������������������������������������������������������������������������������������������� 504 Effects of a Clustered Index ������������������������������������������������������������������������������������������������� 506 Capturing Blocking Information ������������������������������������������������������������������������������������������������ 507 Capturing Blocking Information Using T-SQL ����������������������������������������������������������������������� 507 Extended Events and the blocked_process_report Event ��������������������������������������������������� 510 Recommendations to Reduce Blocking ������������������������������������������������������������������������������������ 513 Summary����������������������������������������������������������������������������������������������������������������������������������� 514 Chapter 17: Causes and Solutions For Deadlocks ������������������������������������������������ 515 Deadlock Fundamentals ����������������������������������������������������������������������������������������������������������� 515 Choosing the Deadlock Victim ��������������������������������������������������������������������������������������������� 517 Analyzing the Causes of Deadlocks ������������������������������������������������������������������������������������������ 517 Capturing Deadlock Information ������������������������������������������������������������������������������������������ 518 Analyzing the Deadlock Graph ��������������������������������������������������������������������������������������������� 521 Error Handling for Deadlocks ���������������������������������������������������������������������������������������������������� 532 Mechanisms to Prevent Deadlocks ������������������������������������������������������������������������������������������� 533 Access Resources in the Same Order ��������������������������������������������������������������������������������� 533 Decrease the Amount of Resources Accessed �������������������������������������������������������������������� 534 Minimize Lock Contention ��������������������������������������������������������������������������������������������������� 535 Summary����������������������������������������������������������������������������������������������������������������������������������� 536 Chapter 18: Row-by-Row Processing from Cursors and Other Causes ���������������� 537 Cursor Fundamentals ���������������������������������������������������������������������������������������������������������������� 537 Cursor Location ������������������������������������������������������������������������������������������������������������������� 538 Cursor Concurrency ������������������������������������������������������������������������������������������������������������� 539 Cursor Types ������������������������������������������������������������������������������������������������������������������������ 541 Cursor Cost Comparison ����������������������������������������������������������������������������������������������������������� 547 Cost Comparison Based on Location ����������������������������������������������������������������������������������� 547 Cost Comparison Based on Concurrency����������������������������������������������������������������������������� 548 Cost Comparison Based on Cursor Type ������������������������������������������������������������������������������ 551 Table of ConTenTs
xvi Default Result Set ��������������������������������������������������������������������������������������������������������������������� 555 Benefits ������������������������������������������������������������������������������������������������������������������������������� 556 Multiple Active Result Sets �������������������������������������������������������������������������������������������������� 556 Drawbacks ��������������������������������������������������������������������������������������������������������������������������� 557 Cursor Overhead ����������������������������������������������������������������������������������������������������������������������� 560 Cursor Recommendations ��������������������������������������������������������������������������������������������������������� 563 Summary����������������������������������������������������������������������������������������������������������������������������������� 564 Chapter 19: Memory-Optimized OLTP Tables and Procedures ������������������������������ 567 In-Memory OLTP Fundamentals ������������������������������������������������������������������������������������������������ 567 System Requirements ��������������������������������������������������������������������������������������������������������� 569 Basic Setup ������������������������������������������������������������������������������������������������������������������������� 569 Creating Tables�������������������������������������������������������������������������������������������������������������������������� 571 In-Memory Indexes �������������������������������������������������������������������������������������������������������������� 577 Statistics Maintenance �������������������������������������������������������������������������������������������������������� 582 Natively Compiled Stored Procedures ��������������������������������������������������������������������������������������� 584 Recommendations �������������������������������������������������������������������������������������������������������������������� 587 Baselines ����������������������������������������������������������������������������������������������������������������������������� 587 Correct Workload ����������������������������������������������������������������������������������������������������������������� 588 Memory Optimization Advisor ���������������������������������������������������������������������������������������������� 588 Native Compilation Advisor �������������������������������������������������������������������������������������������������� 594 Summary����������������������������������������������������������������������������������������������������������������������������������� 596 Chapter 20: Graph Databases ������������������������������������������������������������������������������� 597 Introduction to Graph Databases ���������������������������������������������������������������������������������������������� 597 Querying Graph Data ����������������������������������������������������������������������������������������������������������������� 611 Shortest Path ����������������������������������������������������������������������������������������������������������������������� 613 Performance Considerations of Graph Data ������������������������������������������������������������������������������ 616 Summary����������������������������������������������������������������������������������������������������������������������������������� 617 Table of ConTenTs
xvii Chapter 21: Intelligent Query Processing ������������������������������������������������������������� 619 Adaptive Query Processing ������������������������������������������������������������������������������������������������������� 620 Interleaved Execution ���������������������������������������������������������������������������������������������������������� 620 Query Processing Feedback ������������������������������������������������������������������������������������������������ 630 Approximate Query Processing ������������������������������������������������������������������������������������������������� 645 APPROX_COUNT_DISTINCT �������������������������������������������������������������������������������������������������� 645 APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC ������������������������������������������� 646 Table Variable Deferred Compilation ����������������������������������������������������������������������������������������� 648 Scalar User-Defined Function Inlining ��������������������������������������������������������������������������������������� 650 Summary����������������������������������������������������������������������������������������������������������������������������������� 655 Chapter 22: Automated Tuning in Azure and SQL Server�������������������������������������� 657 Automatic Plan Correction �������������������������������������������������������������������������������������������������������� 657 Tuning Recommendations ��������������������������������������������������������������������������������������������������� 658 Enabling Automatic Tuning �������������������������������������������������������������������������������������������������� 664 Automatic Tuning in Action �������������������������������������������������������������������������������������������������� 667 Azure SQL Database Automatic Index Management ����������������������������������������������������������������� 668 Summary����������������������������������������������������������������������������������������������������������������������������������� 675 Chapter 23: A Query Tuning Methodology ������������������������������������������������������������ 677 Database Design ����������������������������������������������������������������������������������������������������������������������� 677 Use Entity-Integrity Constraints ������������������������������������������������������������������������������������������� 678 Maintain Domain and Referential Integrity Constraints ������������������������������������������������������� 681 Adopt Index-Design Best Practices ������������������������������������������������������������������������������������� 684 Avoid the Use of the “sp_” Prefix for Stored Procedure Names ������������������������������������������ 685 Minimize the Use of Triggers ����������������������������������������������������������������������������������������������� 686 Put Tables into In-Memory Storage ������������������������������������������������������������������������������������� 686 Use Columnstore Indexes ���������������������������������������������������������������������������������������������������� 686 Take Advantage of Graph Storage ��������������������������������������������������������������������������������������� 687 Configuration Settings �������������������������������������������������������������������������������������������������������������� 687 Memory Configuration Options �������������������������������������������������������������������������������������������� 687 Cost Threshold for Parallelism ��������������������������������������������������������������������������������������������� 687 Table of ConTenTs
xviii Max Degree of Parallelism��������������������������������������������������������������������������������������������������� 689 Optimize for Ad Hoc Workloads ������������������������������������������������������������������������������������������� 689 Block Process Threshold ����������������������������������������������������������������������������������������������������� 689 Database Compression �������������������������������������������������������������������������������������������������������� 689 Database Administration ����������������������������������������������������������������������������������������������������������� 690 Keep Statistics Up to Date ��������������������������������������������������������������������������������������������������� 690 Maintain a Minimum Amount of Index Fragmentation �������������������������������������������������������� 690 Avoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINK ������������������������������������ 691 Query Design ����������������������������������������������������������������������������������������������������������������������������� 691 Use the Command SET NOCOUNT ON ���������������������������������������������������������������������������������� 692 Explicitly Define the Owner of an Object ����������������������������������������������������������������������������� 692 Avoid Non-sargable Search Conditions ������������������������������������������������������������������������������� 692 Avoid Arithmetic Expressions on Filter Clauses ������������������������������������������������������������������� 694 Avoid Optimizer Hints ���������������������������������������������������������������������������������������������������������� 695 Stay Away from Nesting Views �������������������������������������������������������������������������������������������� 695 Ensure No Implicit Data Type Conversions �������������������������������������������������������������������������� 696 Minimize Logging Overhead ������������������������������������������������������������������������������������������������ 696 Adopt Best Practices for Reusing Execution Plans �������������������������������������������������������������� 697 Adopt Best Practices for Database Transactions ����������������������������������������������������������������� 698 Eliminate or Reduce the Overhead of Database Cursors ����������������������������������������������������� 699 Use Natively Compiled Stored Procedures �������������������������������������������������������������������������� 699 Take Advantage of Columnstore for Analytical Queries ������������������������������������������������������� 699 Enable Query Store �������������������������������������������������������������������������������������������������������������� 700 Summary����������������������������������������������������������������������������������������������������������������������������������� 700 Index ��������������������������������������������������������������������������������������������������������������������� 701 Table of ConTenTs
xix About the Author Grant Fritchey, Microsoft Data Platform MVP and AWS Community Builder, has more than 30 years of experience in IT. That time was spent in technical support, development, and database administration. He currently works as a Product Advocate at Redgate Software. Grant writes articles for publication at SQL Server Central and Simple Talk. He has published books, including SQL Server Execution Plans and SQL Server 2017 Query Performance Tuning (Apress). He has written chapters for Beginning SQL Server 2012 Administration (Apress), SQL Server Team-based Development, SQL Server MVP Deep Dives Volume 2, Pro SQL Server 2012 Practices (Apress), Expert Performance Indexing in SQL Server (Apress), and Query Store for SQL Server 2019 (Apress). Grant presents live sessions, virtual sessions, and recorded content online, and in person, all around the world.
xxi About the Technical Reviewer John Deardurff has been a Microsoft Certified Trainer (MCT) since July 2000 and specializes in teaching Azure SQL and SQL Server workshops. He is currently a Data and AI Cloud Solutions Architect for Microsoft, an MCT Regional Lead for the Eastern United States, and a former Data Platform MVP.
Comments 0
Loading comments...
Reply to Comment
Edit Comment