Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database, 3rd Edition
- Length: 425 pages
- Edition: 3
- Language: English
- Publisher: Apress
- Publication Date: 2023-02-02
- ISBN-10: 1484287703
- ISBN-13: 9781484287705
- Sales Rank: #5139329 (See Top 100 Books)
Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You’ll learn to identify precisely why your queries are running slowly. And you’ll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.
The book goes beyond the most common wait types into the more complex and performance-threatening wait types. You’ll learn about per-query wait statistics and session-based wait statistics, and the types of problems they can help you solve. The different wait types are categorized by their area of impact, including CPU, IO, Latching, Locking, and many more.
Clear examples are included to help you gain practical knowledge of why and how specific wait times increase or decrease, how they impact your SQL Server’s performance, and what you can do to improve performance. After reading this book, you won’t want to be without the valuable information that wait statistics provide regarding where you should be spending your limited tuning time to maximize performance and value to your business.
What You’ll Learn
- Understand how the SQL Server engine processes requests
- Identify resource bottlenecks in a running SQL Server instance
- Locate wait statistics information inside DMVs and Query Store
- Analyze the root cause of sub-optimal performance
- Diagnose I/O contention and locking contention
- Benchmark SQL Server performance
- Improve database performance by lowering overall wait time
Who This Book Is For
Database administrators who want to identify and resolve performance bottlenecks, those who want to learn more about how the SQL Server engine accesses and uses resources inside SQL Server, and administrators concerned with achieving―and knowing they have achieved―optimal performance
Table of Contents About the Authors About the Technical Reviewers Acknowledgments Introduction 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 Untitled 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 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 Untitled 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
Donate to keep this site alive
How to download source code?
1. Go to: https://github.com/Apress
2. In the Find a repository… box, search the book title: Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database, 3rd Edition
, sometime you may not get the results, please search the main title.
3. Click the book title in the search results.
3. Click Code to download.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.