Pro SQL Server Wait Statistics

Pro SQL Server Wait Statistics

by Enrico van de Laar
Released September 2015
Publisher(s): Apress
ISBN: 9781484211397

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Pro SQL Server Wait Statistics is a practical guide for analyzing and troubleshooting SQL Server performance using wait statistics. Whether you are new to wait statistics, or already familiar with them, this book will help you gain a deeper understanding of how wait statistics are generated and what they can mean for your SQL Server’s performance.

Besides the most common wait types, Pro SQL Server Wait Statistics goes further into the more complex and performance threatening wait types. The different wait types are categorized by their area of impact, and include CPU, IO, Lock, and many more different wait type categories. Filled with clear examples, Pro SQL Server Wait Statistics helps you gain practical knowledge of why and how specific wait times increase or decrease, and how they impact your SQL Server’s performance.

Table of contents

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

Product information

  • Title: Pro SQL Server Wait Statistics
  • Author(s): Enrico van de Laar
  • Release date: September 2015
  • Publisher(s): Apress
  • ISBN: 9781484211397