Table Of ContentSQL Server Advanced
Troubleshooting and
Performance Tuning
Best Practices and Techniques
Dmitri Korotkevitch
SQL Server Advanced Troubleshooting
and Performance Tuning
This practical book provides a comprehensive overview of
troubleshooting and performance tuning best practices for “A great collection
Microsoft SQL Server. Database engineers, including database
of techniques, tips,
developers and administrators, will learn how to identify
and scripts to deal
performance issues, troubleshoot the system in a holistic
with problems in both
fashion, and properly prioritize tuning efforts to attain the best
system performance possible. reactive and proactive
ways. I wish I’d had a
Author Dmitri Korotkevitch, Microsoft Data Platform
MVP and Microsoft Certified Master (MCM), explains book like this 25 years
the interdependencies between SQL Server database ago when I started
components. You’ll learn how to quickly diagnose your system working with SQL Server.”
and discover the root cause of any issue. Techniques in this
—Thomas Grohser
book are compatible with all versions of SQL Server and cover SQL Server Infrastructure Architect
both on-premises and cloud-based SQL Server installations.
• Discover how performance issues present themselves in
Dmitri Korotkevitch is a Microsoft
SQL Server
Data Platform MVP and Microsoft
• Learn about SQL Server diagnostic tools, methods, and Certified Master (SQL Server) with
technologies years of IT experience, including
working with Microsoft SQL Server as
• Perform health checks on SQL Server installations
an application and database developer,
• Learn the dependencies between SQL Server components database administrator, and database
architect. Dmitri specializes in the
• Tune SQL Server to improve performance and reduce
design, development, and performance
bottlenecks tuning of complex OLTP systems that
• Detect poorly optimized queries and inefficiencies in query handle thousands of transactions per
second around the clock. Currently,
execution plans
he leads the Database Services team
• Find inefficient indexes and common database design issues at Chewy.com and he provides SQL
Server consulting services and training
• Use these techniques with Microsoft Azure SQL databases,
to clients around the world.
Azure SQL Managed Instances, and Amazon RDS for SQL
Server
DATA Twitter: @oreillymedia
linkedin.com/company/oreilly-media
US $69.99 CAN $87.99 youtube.com/oreillymedia
ISBN: 978-1-098-10192-3
SQL Server Advanced
Troubleshooting and
Performance Tuning
Best Practices and Techniques
Dmitri Korotkevich
BBeeiijjiinngg BBoossttoonn FFaarrnnhhaamm SSeebbaassttooppooll TTookkyyoo
SQL Server Advanced Troubleshooting and Performance Tuning
by Dmitri Korotkevitch
Copyright © 2022 Dmitri Korotkevitch. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional
sales department: 800-998-9938 or [email protected].
Acquisitions Editor: Andy Kwan Indexer: Potomac Indexing, LLC
Development Editor: Sarah Grey Interior Designer: David Futato
Production Editor: Beth Kelly Cover Designer: Karen Montgomery
Copyeditor: Audrey Doyle Illustrator: Kate Dullea
Proofreader: Piper Editorial Consulting, LLC
May 2022: First Edition
Revision History for the First Edition
2022-05-13: First Release
See http://oreilly.com/catalog/errata.csp?isbn=9781098101923 for release details.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. SQL Server Advanced Troubleshooting
and Performance Tuning, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc.
The views expressed in this work are those of the author, and do not represent the publisher’s views.
While the publisher and the author have used good faith efforts to ensure that the information and
instructions contained in this work are accurate, the publisher and the author disclaim all responsibility
for errors or omissions, including without limitation responsibility for damages resulting from the use
of or reliance on this work. Use of the information and instructions contained in this work is at your
own risk. If any code samples or other technology this work contains or describes is subject to open
source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use
thereof complies with such licenses and/or rights.
978-1-098-10192-3
[LSI]
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
1. SQL Server Setup and Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Hardware and Operating System Considerations 2
CPU 2
Memory 2
Disk Subsystem 3
Network 4
Operating Systems and Applications 5
Virtualization and Clouds 6
Configuring Your SQL Server 7
SQL Server Version and Patching Level 7
Instant File Initialization 7
tempdb Configuration 9
Trace Flags 10
Server Options 12
Configuring Your Databases 15
Database Settings 15
Transaction Log Settings 17
Data Files and Filegroups 17
Analyzing the SQL Server Error Log 19
Consolidating Instances and Databases 22
Observer Effect 23
Summary 25
Troubleshooting Checklist 26
2. SQL Server Execution Model and Wait Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
SQL Server: High-Level Architecture 27
iii
SQLOS and the Execution Model 29
Wait Statistics 32
Execution Model–Related Dynamic Management Views 36
sys.dm_os_wait_stats 37
sys.dm_exec_session_wait_stats 38
sys.dm_os_waiting_tasks 38
sys.dm_exec_requests 39
sys.dm_os_schedulers 41
Resource Governor Overview 42
Summary 44
Troubleshooting Checklist 44
3. Disk Subsystem Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Anatomy of the SQL Server I/O Subsystem 45
Scheduling and I/O 46
Data Reads 47
Data Writes 49
The Storage Subsystem: A Holistic View 50
sys.dm_io_virtual_file_stats view 51
Performance Counters and OS Metrics 55
Virtualization, HBA, and Storage Layers 59
Checkpoint Tuning 61
I/O Waits 64
ASYNC_IO_COMPLETION Waits 64
IO_COMPLETION Waits 64
WRITELOG Waits 65
WRITE_COMPLETION Waits 65
PAGEIOLATCH Waits 65
Summary 67
Troubleshooting Checklist 67
4. Inefficient Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
The Impact of Inefficient Queries 69
Plan Cache–Based Execution Statistics 70
Extended Events and SQL Traces 82
Query Store 88
Query Store SSMS Reports 92
Working with Query Store DMVs 96
Third-Party Tools 101
Summary 102
Troubleshooting Checklist 103
iv | Table of Contents
5. Data Storage and Query Tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Data Storage and Access Patterns 105
Row-Based Storage Tables 106
B-Tree Indexes 109
Composite Indexes 114
Nonclustered Indexes 115
Index Fragmentation 120
Statistics and Cardinality Estimation 123
Statistics Maintenance 126
Cardinality Estimation Models 128
Analyzing Your Execution Plan 130
Row Mode and Batch Mode Execution 130
Live Query Statistics and Execution Statistics Profiling 133
Common Issues and Inefficiencies 137
Inefficient Code 137
Inefficient Index Seek 140
Incorrect Join Type 143
Excessive Key Lookups 151
Indexing the Data 154
Summary 157
Troubleshooting Checklist 157
6. CPU Load. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Nonoptimized Queries and T-SQL Code 159
Inefficient T-SQL Code 160
Scripts for Troubleshooting High CPU Load 161
Nonoptimized Query Patterns to Watch For 163
Query Compilation and Plan Caching 164
Parameter-Sensitive Plans 165
Parameter-Value Independence 171
Compilation and Parameterization 174
Auto-Parameterization 176
Simple Parameterization 177
Forced Parameterization 177
Parallelism 180
Summary 182
Troubleshooting Checklist 183
7. Memory Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
SQL Server Memory Usage and Configuration 185
Configuring SQL Server Memory 188
How Much Memory Is Enough? 190
Table of Contents | v
Memory Allocations 190
Memory Clerks 192
The DBCC MEMORYSTATUS Command 202
Query Execution and Memory Grants 202
Optimizing Memory-Intensive Queries 206
Memory Grant Feedback 211
Controlling Memory Grant Size 212
In-Memory OLTP Memory Usage and Troubleshooting 214
Summary 217
Troubleshooting Checklist 217
8. Locking, Blocking, and Concurrency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Lock Types and Locking Behavior 220
Major Lock Types 221
Lock Compatibility 224
Transaction Isolation Levels and Locking Behavior 225
Blocking Issues 228
Troubleshooting Real-Time Blocking 230
Working with Blocked Process Reports 234
Event Notifications and Blocking Monitoring Framework 238
Deadlocks 238
Troubleshooting Deadlocks 240
Locking and Indexes 244
Optimistic Isolation Levels 245
READ COMMITTED SNAPSHOT Isolation Level 247
SNAPSHOT Isolation Level 248
Schema Locks 249
Lock Escalation 251
Lock Escalation Troubleshooting 254
Locking-Related Waits 256
LCK_M_U Wait Type 257
LCK_M_S Wait Type 257
LCK_M_X Wait Type 258
LCK_M_SCH_S and LCK_M_SCH_M Wait Types 258
Intent LCK_M_I* Wait Types 259
Range Locks LCK_M_R* Wait Types 259
Summary 260
Troubleshooting Checklist 260
9. tempdb Usage and Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Temporary Objects: Usage and Best Practices 263
Temporary Tables and Table Variables 264
vi | Table of Contents
Temporary Object Caching 270
Table-Valued Parameters 272
Regular Tables in tempdb and Transaction Logging 274
Internal tempdb Consumers 275
Version Store 275
Spills 278
Common tempdb Issues 281
System Page Contention 283
Running Out of Space 287
tempdb Configuration 290
Summary 291
Troubleshooting Checklist 292
10. Latches. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Introduction to Latches 293
Page Latches 295
Addressing Hotspots: The OPTIMIZE_FOR_SEQUENTIAL_KEY
Index Option 299
Addressing Hotspots: Hash Partitioning 300
Addressing Hotspots: In-Memory OLTP 302
Other Latch Types 303
Summary 305
Troubleshooting Checklist 306
11. Transaction Log. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Transaction Log Internals 307
Data Modifications and Transaction Logging 308
Explicit and Auto-Committed Transactions and Log Overhead 313
Delayed Durability 316
In-Memory OLTP Transaction Logging 317
Virtual Log Files 317
Transaction Log Configuration 321
Log Truncation Issues 321
LOG_BACKUP Log Reuse Wait 323
ACTIVE_TRANSACTION Log Reuse Wait 323
AVAILABILITY_REPLICA Log Reuse Wait 325
DATABASE_MIRRORING Log Reuse Wait 325
REPLICATION Log Reuse Wait 325
ACTIVE_BACKUP_OR_RESTORE Log Reuse Wait 326
Other Mitigation Strategies 326
Accelerated Database Recovery 326
Transaction Log Throughput 327
Table of Contents | vii
Summary 329
Troubleshooting Checklist 330
12. AlwaysOn Availability Groups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
AlwaysOn Availability Groups Overview 331
Availability Group Queues 333
Synchronous Replication and the Danger of the HADR_SYNC_COMMIT
Wait 337
Availability Group Extended Events 340
Asynchronous Replication and Readable Secondaries 345
The Impact of Readable Secondaries 346
Parallel Redo 350
Troubleshooting Failover Events 351
Availability Groups and Windows Server Failover Cluster 351
Troubleshooting Failovers 353
When a Failover Does Not Occur 355
Summary 355
Troubleshooting Checklist 356
13. Other Notable Wait Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
ASYNC_NETWORK_IO Waits 357
THREADPOOL Waits 359
Backup-Related Waits 364
Improving Backup Performance 364
BUFFERCOUNT and MAXTRANSFERSIZE Options 365
Partial Database Backups 365
HTBUILD and Other HT* Waits 366
Preemptive Waits 366
PREEMPTIVE_OS_WRITEFILEGATHER Wait Type 367
PREEMPTIVE_OS_WRITEFILE Wait Type 367
Authentication-Related Wait Types 367
OLEDB Waits 368
Wait Types: Wrapping Up 368
Summary 369
Troubleshooting Checklist 370
14. Database Schema and Index Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
Database Schema Analysis 371
Heap Tables 372
Indexes with the uniqueidentifier Data Type 375
Wide and Nonunique Clustered Indexes 376
Untrusted Foreign Keys 378
viii | Table of Contents
