SQL Server: Performance Troubleshooting Using Wait Statistics

Pluralsight

Course Summary

Learn how to begin troubleshooting SQL Server performance problems, applicable for developers and DBAs from SQL Server 2005 onwards

+
Course Description

Where do you start looking when trying to diagnose a performance problem with SQL Server? The answer is to use wait statistics! This course will introduce you to the powerful 'waits and queues' performance tuning methodology. You'll learn how SQL Server's thread scheduling system works, what wait statistics are and how to use them, what more advanced synchronization mechanisms like latches and spinlocks are, and a wealth of detail about common performance issues and how to diagnose and solve them. The course is perfect for developers and DBAs who have been struggling to figure out how to start troubleshooting performance problems with SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.

Course Description

Where do you start looking when trying to diagnose a performance problem with SQL Server? The answer is to use wait statistics! This course will introduce you to the powerful 'waits and queues' performance tuning methodology. You'll learn how SQL Server's thread scheduling system works, what wait statistics are and how to use them, what more advanced synchronization mechanisms like latches and spinlocks are, and a wealth of detail about common performance issues and how to diagnose and solve them. The course is perfect for developers and DBAs who have been struggling to figure out how to start troubleshooting performance problems with SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.

+
Course Syllabus

Introduction
- 15m 9s

—Introduction 1m 47s
—Where to Start Troubleshooting? 2m 0s
—Doctor, Doctor... My Knee Hurts! 2m 14s
—Is the Symptom the Root Cause? 2m 38s
—Interpreting the Data 2m 33s
—Demo: Explaining the Workload Mechanism 2m 30s
—Course Structure 1m 27s

SQL Server Threading Model
- 35m 10s

—Introduction 1m 22s
—What are Threads? 2m 7s
—Parallelism 2m 6s
—Parallel Threads Example 1m 6s
—Controlling Parallelism 2m 6s
—Thread Scheduling 1m 47s
—Components of a Scheduler 0m 53s
—Schedulers in SQL Server 1m 52s
—Demo: sys.dm_os_schedulers 2m 8s
—Thread States 1m 22s
—Transition from RUNNING to SUSPENDED 0m 51s
—The Waiter List 1m 22s
—Special Case: Quantum Exhaustion 1m 43s
—Transition from SUSPENDED to RUNNABLE 1m 23s
—The Runnable Queue 1m 31s
—Special Case: Resource Governor 2m 16s
—Resource Governor Example 2m 1s
—Transition from RUNNABLE to RUNNING 0m 47s
—Putting It All Together 1m 31s
—Demo: Examining Threads on Schedulers 3m 19s
—Summary 1m 37s

Waits
- 43m 18s

—Introduction 1m 6s
—What are Waits? 1m 57s
—What are Queues? 1m 53s
—Waits and Queues Methodology 2m 15s
—Wait Times Defined 1m 30s
—Graphical View of Wait Times 1m 16s
—sys.dm_os_waiting_tasks DMV 2m 17s
—Demo: sys.dm_os_waiting_tasks 4m 52s
—sys.dm_os_wait_stats DMV 1m 25s
—Filtering Benign Waits 2m 36s
—Demo: sys.dm_os_wait_stats 5m 21s
—Storing Wait Statistics 1m 41s
—Clearing Wait Statistics 1m 5s
—Demo: Clearing Wait Statistics 3m 44s
—Using Extended Events 2m 48s
—Demo: Using Extended Events 6m 13s
—Summary 1m 19s

Latches and Spinlocks
- 33m 12s

—Introduction 1m 3s
—What are Latches? 2m 41s
—Types of Latches 1m 32s
—B-tree Page Split Example 2m 53s
—Latch Contention 2m 15s
—Tempdb Latch Contention Example 3m 5s
—sys.dm_os_latch_stats DMV 1m 5s
—Demo: sys.dm_os_latch_stats 2m 7s
—What are Spinlocks? 2m 1s
—Spinlock Internals 2m 17s
—Spinlock Contention 2m 13s
—sys.dm_os_spinlock_stats DMV 1m 21s
—Demo: sys.dm_os_spinlock_stats 3m 21s
—Clearing Latch and Spinlock Statistics 1m 15s
—Using Extended Events 1m 13s
—Transaction Log Example 1m 20s
—Summary 1m 25s

Troubleshooting Patterns
- 2h 4m

—Introduction 1m 13s
—What is Relevant? 1m 29s
—Top Wait Types Worldwide Survey 0m 53s
—CXPACKET Wait Explanation 2m 10s
—CXPACKET Wait Example of Non-Skewed Scan 1m 10s
—CXPACKET Wait Example of Skewed Scan 1m 57s
—CXPACKET Wait Solutions 3m 14s
—Demo: CXPACKET Waits 7m 12s
—PAGEIOLATCH_XX Wait 3m 25s
—PAGEIOLATCH_XX Wait Solutions 1m 36s
—Demo: PAGEIOLATCH_XX Waits 1m 35s
—ASYNC_NETWORK_IO Wait 2m 42s
—Demo: ASYNC_NETWORK_IO Waits 2m 12s
—WRITELOG Wait 2m 31s
—WRITELOG Wait Solutions 2m 16s
—Demo: WRITELOG Waits 5m 38s
—PAGELATCH_XX Wait 1m 30s
—PAGELATCH_XX Wait Solutions 2m 18s
—PAGELATCH_XX Wait Solutions (part 2) 2m 11s
—Demo: PAGELATCH_XX Waits 2m 46s
—LCK_M_XX Wait 1m 52s
—LCK_M_XX Wait Solutions 3m 7s
—Demo: LCK_M_XX Waits 6m 26s
—SOS_SCHEDULER_YIELD Wait 2m 31s
—SOS_SCHEDULER_YIELD Wait Solutions 1m 29s
—Using Extended Events to Examine Call Stacks 2m 11s
—Demo: Examining Call Stacks with Extended Events 6m 9s
—OLEDB Wait 1m 21s
—PREEMPTIVE_OS_XX Waits 2m 18s
—PREEMPTIVE_OS_CREATEFILE Wait 2m 3s
—Demo: PREEMPTIVE_OS_CREATEFILE Wait 3m 7s
—PREEMPTIVE_OS_WRITEFILEGATHER Wait 2m 6s
—PREEMPTIVE_OS_WAITFORSINGLEOBJECT Wait 1m 36s
—BACKUPXX Waits 1m 28s
—Demo: BACKUPXX Waits 1m 57s
—DBMIRRORXX Waits 1m 51s
—HADR_XX Waits 2m 18s
—TRACEWRITE and SQLTRACE_XX Waits 1m 27s
—LATCH_XX Waits 1m 25s
—ACCESS_METHODS_XX Latches 2m 16s
—Demo: ACCESS_METHODS_XX Latches 3m 0s
—FGCB_ADD_REMOVE Latch 2m 21s
—Demo: FGCB_ADD_REMOVE Latches 7m 59s
—DBCC_XX Latches 1m 41s
—Demo: DBCC_XX Latches 1m 12s
—Miscellaneous Wait Types 1m 57s
—More Miscellaneous Wait Types 2m 3s
—Miscellaneous Latch Classes 1m 55s
—Miscellaneous Spinlocks 1m 59s
—Summary 1m 8s

Summary
- 20m 54s

—Introduction 0m 47s
—Waits, Latches and Spinlocks 1m 17s
—Methodology: No Historical Data 2m 35s
—Methodology: Historical Data 2m 7s
—Real-World Example: Symptoms 2m 8s
—Real-World Example: Analysis 2m 12s
—Real-World Example: Root-Causes 3m 7s
—Real-World Example: Solution 2m 2s
—Resources 1m 57s
—Summary 2m 42s