Pro SQL Server 2022 Wait Statistics

✍ Scribed by Thomas LaRock , Enrico van de Laar

• Publisher: Apress
• Year: 2023
• Tongue: English
• Leaves: 412
• Edition: Third
• Category: Library

✦ Table of Contents

Table of Contents
About the Authors
About the Technical Reviewers
Acknowledgments
Introduction
Part I: Foundations of Wait Statistics Analysis
Chapter 1: Wait Statistics Internals
A Brief History of Wait Statistics
The SQLOS
Schedulers, Tasks, and Worker Threads
Sessions
Requests
Tasks
Worker Threads
Schedulers
Putting It All Together
Wait Statistics
Summary
Chapter 2: Querying SQL Server Wait Statistics
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
Understanding sys.dm_os_waiting_tasks
Querying sys.dm_os_waiting_tasks
sys.dm_exec_requests
Understanding sys.dm_exec_requests
Querying sys.dm_exec_requests
sys.dm_exec_session_wait_stats
Combining DMVs to Detect Waits Happening Now
Viewing Wait Statistics Using Perfmon
Capturing Wait Statistics Using Extended Events
Capture Wait Statistics Information for a Specific Query
Analyzing Wait Statistics on a Per-Query Basis Using Execution Plans
Summary
Chapter 3: The Query Store
What Is the Query Store?
Enabling the Query Store
Enable the Query Store Using SSMS
Enable the Query Store Using T-SQL
Query Store Architecture
How Wait Statistics Are Processed in the Query Store
Accessing Wait Statistics Through Query Store Reports
Accessing Wait Statistics Through Query Store DMVs
Summary
Chapter 4: Building a Solid Baseline
What Are Baselines?
Visualizing Your Baselines
Baseline Types and Statistics
Baseline Pitfalls
Too Much Information
Know Your Metrics
Find the Big Measurement Changes
Use Fixed Intervals
Building a Baseline for Wait Statistics Analysis
Reset Capture Method
Delta Capture Method
Using SQL Server Agent to Schedule Measurements
Wait Statistics Baseline Analysis
Summary
Part II: Wait Types
Chapter 5: CPU-Related Wait Types
CXPACKET
What Is the CXPACKET Wait Type?
Lowering CXPACKET Wait Time by Tuning the Parallelism Configuration Options
Lowering CXPACKET Wait Time by Resolving Skewed Workloads
Introduction of the CXCONSUMER Wait Type
CXPACKET Summary
SOS_SCHEDULER_YIELD
What Is the SOS_SCHEDULER_YIELD Wait Type?
Lowering SOS_SCHEDULER_YIELD Waits
SOS_SCHEDULER_YIELD Summary
THREADPOOL
What Is the THREADPOOL Wait Type?
THREADPOOL Example
Gaining Access to Our SQL Server During THREADPOOL Waits
Lowering THREADPOOL Waits Caused by Parallelism
Lowering THREADPOOL Waits Caused by User Connections
THREADPOOL Summary
Chapter 6: IO-Related Wait Types
ASYNC_IO_COMPLETION
What Is the ASYNC_IO_COMPLETION Wait Type?
ASYNC_IO_COMPLETION Example
Lowering ASYNC_IO_COMPLETION Waits
ASYNC_IO_COMPLETION Summary
ASYNC_NETWORK_IO
What Is the ASYNC_NETWORK_IO Wait Type?
ASYNC_NETWORK_IO Example
Lowering ASYNC_NETWORK_IO Waits
ASYNC_NETWORK_IO Summary
CMEMTHREAD
What Is the CMEMTHREAD Wait Type?
Lowering CMEMTHREAD Waits
CMEMTHREAD Summary
IO_COMPLETION
What Is the IO_COMPLETION Wait Type?
IO_COMPLETION Example
Lowering IO_COMPLETION Waits
IO_COMPLETION Summary
LOGBUFFER and WRITELOG
What Are the LOGBUFFER and WRITELOG Wait Types?
LOGBUFFER and WRITELOG Example
Lowering LOGBUFFER and WRITELOG Waits
LOGBUFFER and WRITELOG Summary
RESOURCE_SEMAPHORE
What Is the RESOURCE_SEMAPHORE Wait Type?
RESOURCE_SEMAPHORE Example
Lowering RESOURCE_SEMAPHORE Waits
RESOURCE_SEMAPHORE Summary
RESOURCE_SEMAPHORE_QUERY_COMPILE
What Is the RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Type?
RESOURCE_SEMAPHORE_QUERY_COMPILE Example
Lowering RESOURCE_SEMAPHORE_QUERY_COMPILE Waits
RESOURCE_SEMAPHORE_QUERY_COMPILE Summary
SLEEP_BPOOL_FLUSH
What Is the SLEEP_BPOOL_FLUSH Wait Type?
SLEEP_BPOOL_FLUSH Example
Lowering SLEEP_BPOOL_FLUSH Waits
SLEEP_BPOOL_FLUSH Summary
WRITE_COMPLETION
What Is the WRITE_COMPLETION Wait Type?
WRITE_COMPLETION Example
Lowering WRITE_COMPLETION Waits
WRITE_COMPLETION Summary
Chapter 7: Backup-Related Wait Types
BACKUPBUFFER
What Is the BACKUPBUFFER Wait Type?
BACKUPBUFFER Example
Lowering BACKUPBUFFER Waits
BACKUPBUFFER Summary
BACKUPIO
What Is the BACKUPIO Wait Type?
BACKUPIO Example
Lowering BACKUPIO Waits
BACKUPIO Summary
BACKUPTHREAD
What Is the BACKUPTHREAD Wait Type?
BACKUPTHREAD Example
Lowering BACKUPTHREAD Waits
BACKUPTHREAD Summary
Chapter 8: Lock-Related Wait Types
Introduction to Locking and Blocking
Lock Modes and Compatibility
Locking Hierarchy
Isolation Levels
Querying Lock Information
LCK_M_S
What Is the LCK_M_S Wait Type?
LCK_M_S Example
Lowering LCK_M_S Waits
LCK_M_S Summary
LCK_M_U
What Is the LCK_M_U Wait Type?
LCK_M_U Example
Lowering LCK_M_U Waits
LCK_M_U Summary
LCK_M_X
What Is the LCK_M_X Wait Type?
LCK_M_X Example
Lowering LCK_M_X Waits
LCK_M_X Summary
LCK_M_I[xx]
What Is the LCK_M_I[xx] Wait Type?
LCK_M_I[xx] Example
Lowering LCK_M_I[xx] Waits
LCK_M_I[xx] Summary
LCK_M_SCH_S and LCK_M_SCH_M
What Are the LCK_M_SCH_S and LCK_M_SCH_M Wait Types?
LCK_M_SCH_S and LCK_M_SCH_M Example
Lowering LCK_M_SCH_S and LCK_M_SCH_M Waits
LCK_M_SCH_S and LCK_M_SCH_M Summary
Chapter 9: Latch-Related Wait Types
Introduction to Latches
Latch Modes
Latch Waits
sys.dm_os_latch_stats
Page-Latch Contention
PAGELATCH_[xx]
What Is the PAGELATCH_[xx] Wait Type?
PAGELATCH_[xx] Example
Lowering PAGELATCH_[xx] Waits
PAGELATCH_[xx] Summary
LATCH_[xx]
What Is the LATCH_[xx] Wait Type?
LATCH_[xx] Example
Lowering LATCH_[xx] Waits
LATCH_[xx] Summary
PAGEIOLATCH_[xx]
What Is the PAGEIOLATCH_[xx] Wait Type?
PAGEIOLATCH_[xx] Example
Lowering PAGEIOLATCH_[xx] Waits
PAGEIOLATCH_[xx] Summary
Chapter 10: High-Availability and Disaster-Recovery Wait Types
DBMIRROR_SEND
What Is the DBMIRROR_SEND Wait Type?
DBMIRROR_SEND Example
Lowering DBMIRROR_SEND Waits
DBMIRROR_SEND Summary
HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE
What Are the HADR_LOGCAPTURE_WAIT and  HADR_WORK_QUEUE Wait Types?
HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE Summary
HADR_SYNC_COMMIT
What Is the HADR_SYNC_COMMIT Wait Type?
HADR_SYNC_COMMIT Example
Lowering HADR_SYNC_COMMIT Waits
HADR_SYNC_COMMIT Summary
REDO_THREAD_PENDING_WORK
What Is the REDO_THREAD_PENDING_WORK Wait Type?
REDO_THREAD_PENDING_WORK Summary
Chapter 11: Preemptive Wait Types
SQL Server on Linux
PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE
What Are the PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Wait Types?
PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Example
Lowering PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Waits
PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE Summary
PREEMPTIVE_OS_WRITEFILEGATHER
What Is the PREEMPTIVE_OS_WRITEFILEGATHER Wait Type?
PREEMPTIVE_OS_WRITEFILEGATHER Example
Lowering PREEMPTIVE_OS_WRITEFILEGATHER Waits
PREEMPTIVE_OS_WRITEFILEGATHER Summary
PREEMPTIVE_OS_AUTHENTICATIONOPS
What Is the PREEMPTIVE_OS_AUTHENTICATIONOPS Wait Type?
PREEMPTIVE_OS_AUTHENTICATIONOPS Example
Lowering PREEMPTIVE_OS_AUTHENTICATIONOPS Waits
PREEMPTIVE_OS_AUTHENTICATIONOPS Summary
PREEMPTIVE_OS_GETPROCADDRESS
What Is the PREEMPTIVE_OS_GETPROCADDRESS Wait Type?
PREEMPTIVE_OS_GETPROCADDRESS Example
Lowering PREEMPTIVE_OS_GETPROCADDRESS Waits
PREEMPTIVE_OS_GETPROCADDRESS Summary
Chapter 12: Background and Miscellaneous Wait Types
CHECKPOINT_QUEUE
What Is the CHECKPOINT_QUEUE Wait Type?
CHECKPOINT_QUEUE Summary
DIRTY_PAGE_POLL
What Is the DIRTY_PAGE_POLL Wait Type?
DIRTY_PAGE_POLL Summary
LAZYWRITER_SLEEP
What Is the LAZYWRITER_SLEEP Wait Type?
LAZYWRITER_SLEEP Summary
MSQL_XP
What Is the MSQL_XP Wait Type?
MSQL_XP Example
Lowering MSQL_XP Waits
MSQL_XP Summary
OLEDB
What Is the OLEDB Wait Type?
OLEDB Example
Lowering OLEDB Waits
OLEDB Summary
TRACEWRITE
What Is the TRACEWRITE Wait Type?
TRACEWRITE Example
Lowering TRACEWRITE Waits
TRACEWRITE Summary
WAITFOR
What Is the WAITFOR Wait Type?
WAITFOR Example
WAITFOR Summary
Chapter 13: In-Memory OLTP–Related Wait Types
Introduction to In-Memory OLTP
Checkpoint File Pairs (CFPs)
Isolation
Transaction Log Changes
WAIT_XTP_HOST_WAIT
What Is the WAIT_XTP_HOST_WAIT Wait Type?
WAIT_XTP_HOST_WAIT Summary
WAIT_XTP_CKPT_CLOSE
What Is the WAIT_XTP_CKPT_CLOSE Wait Type?
WAIT_XTP_CKPT_CLOSE Summary
WAIT_XTP_OFFLINE_CKPT_NEW_LOG
What Is the WAIT_XTP_OFFLINE_CKPT_NEW_LOG Wait Type?
WAIT_XTP_OFFLINE_CKPT_NEW_LOG Summary
Appendix I: Example SQL Server Machine Configurations
Default Test Machine
HA/DR Test Machines
Appendix II: Spinlocks
Appendix III: Latch Classes
Appendix IV: Waits and DMVs
Index