SQL Server: Performance Troubleshooting Using Wait Statistics
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.
Authored by:
Paul Randal
Duration: 4h 31m
Level: Intermediate
Released: 6/28/2012
Features:
Duration: 4h 31m
Level: Intermediate
Released: 6/28/2012
Features:
Course Rating:
You are currently not signed in. Please sign in to access subscriber-only content.
Currently using: {{getCurrentPlayerName()}} [Change]
| expand all | collapse all | Progress | Duration | |
|---|---|---|---|
 Introduction |
|
00:15:09 | |
|
Introduction
|
|
01:47 | |
|
Where to Start Troubleshooting?
|
|
02:00 | |
|
Doctor, Doctor... My Knee Hurts!
|
|
02:14 | |
|
Is the Symptom the Root Cause?
|
|
02:38 | |
|
Interpreting the Data
|
|
02:33 | |
|
Demo: Explaining the Workload Mechanism
|
|
02:30 | |
|
Course Structure
|
|
01:27 | |
|
SQL Server Threading Model |
|
00:35:10 | |
|
Introduction
|
|
01:22 | |
|
What are Threads?
|
|
02:07 | |
|
Parallelism
|
|
02:06 | |
|
Parallel Threads Example
|
|
01:06 | |
|
Controlling Parallelism
|
|
02:06 | |
|
Thread Scheduling
|
|
01:47 | |
|
Components of a Scheduler
|
|
00:53 | |
|
Schedulers in SQL Server
|
|
01:52 | |
|
Demo: sys.dm_os_schedulers
|
|
02:08 | |
|
Thread States
|
|
01:22 | |
|
Transition from RUNNING to SUSPENDED
|
|
00:51 | |
|
The Waiter List
|
|
01:22 | |
|
Special Case: Quantum Exhaustion
|
|
01:43 | |
|
Transition from SUSPENDED to RUNNABLE
|
|
01:23 | |
|
The Runnable Queue
|
|
01:31 | |
|
Special Case: Resource Governor
|
|
02:16 | |
|
Resource Governor Example
|
|
02:01 | |
|
Transition from RUNNABLE to RUNNING
|
|
00:47 | |
|
Putting It All Together
|
|
01:31 | |
|
Demo: Examining Threads on Schedulers
|
|
03:19 | |
|
Summary
|
|
01:37 | |
|
Waits |
|
00:43:18 | |
|
Introduction
|
|
01:06 | |
|
What are Waits?
|
|
01:57 | |
|
What are Queues?
|
|
01:53 | |
|
Waits and Queues Methodology
|
|
02:15 | |
|
Wait Times Defined
|
|
01:30 | |
|
Graphical View of Wait Times
|
|
01:16 | |
|
sys.dm_os_waiting_tasks DMV
|
|
02:17 | |
|
Demo: sys.dm_os_waiting_tasks
|
|
04:52 | |
|
sys.dm_os_wait_stats DMV
|
|
01:25 | |
|
Filtering Benign Waits
|
|
02:36 | |
|
Demo: sys.dm_os_wait_stats
|
|
05:21 | |
|
Storing Wait Statistics
|
|
01:41 | |
|
Clearing Wait Statistics
|
|
01:05 | |
|
Demo: Clearing Wait Statistics
|
|
03:44 | |
|
Using Extended Events
|
|
02:48 | |
|
Demo: Using Extended Events
|
|
06:13 | |
|
Summary
|
|
01:19 | |
|
Latches and Spinlocks |
|
00:32:49 | |
|
Introduction
|
|
01:04 | |
|
What are Latches?
|
|
02:41 | |
|
Types of Latches
|
|
01:32 | |
|
B-tree Page Split Example
|
|
02:53 | |
|
Latch Contention
|
|
02:05 | |
|
Tempdb Latch Contention Example
|
|
03:05 | |
|
sys.dm_os_latch_stats DMV
|
|
01:05 | |
|
Demo: sys.dm_os_latch_stats
|
|
02:07 | |
|
What are Spinlocks?
|
|
02:01 | |
|
Spinlock Internals
|
|
02:18 | |
|
Spinlock Contention
|
|
02:02 | |
|
sys.dm_os_spinlock_stats DMV
|
|
01:21 | |
|
Demo: sys.dm_os_spinlock_stats
|
|
03:21 | |
|
Clearing Latch and Spinlock Statistics
|
|
01:15 | |
|
Using Extended Events
|
|
01:14 | |
|
Transaction Log Example
|
|
01:20 | |
|
Summary
|
|
01:25 | |
|
Troubleshooting Patterns |
|
02:04:11 | |
|
Introduction
|
|
01:13 | |
|
What is Relevant?
|
|
01:29 | |
|
Top Wait Types Worldwide Survey
|
|
00:53 | |
|
CXPACKET Wait Explanation
|
|
02:10 | |
|
CXPACKET Wait Example of Non-Skewed Scan
|
|
01:10 | |
|
CXPACKET Wait Example of Skewed Scan
|
|
01:57 | |
|
CXPACKET Wait Solutions
|
|
03:14 | |
|
Demo: CXPACKET Waits
|
|
07:12 | |
|
PAGEIOLATCH_XX Wait
|
|
03:25 | |
|
PAGEIOLATCH_XX Wait Solutions
|
|
01:36 | |
|
Demo: PAGEIOLATCH_XX Waits
|
|
01:35 | |
|
ASYNC_NETWORK_IO Wait
|
|
02:42 | |
|
Demo: ASYNC_NETWORK_IO Waits
|
|
02:12 | |
|
WRITELOG Wait
|
|
02:31 | |
|
WRITELOG Wait Solutions
|
|
02:16 | |
|
Demo: WRITELOG Waits
|
|
05:38 | |
|
PAGELATCH_XX Wait
|
|
01:30 | |
|
PAGELATCH_XX Wait Solutions
|
|
02:18 | |
|
PAGELATCH_XX Wait Solutions (part 2)
|
|
02:11 | |
|
Demo: PAGELATCH_XX Waits
|
|
02:46 | |
|
LCK_M_XX Wait
|
|
01:52 | |
|
LCK_M_XX Wait Solutions
|
|
03:07 | |
|
Demo: LCK_M_XX Waits
|
|
06:26 | |
|
SOS_SCHEDULER_YIELD Wait
|
|
02:31 | |
|
SOS_SCHEDULER_YIELD Wait Solutions
|
|
01:29 | |
|
Using Extended Events to Examine Call Stacks
|
|
02:11 | |
|
Demo: Examining Call Stacks with Extended Events
|
|
06:09 | |
|
OLEDB Wait
|
|
01:21 | |
|
PREEMPTIVE_OS_XX Waits
|
|
02:18 | |
|
PREEMPTIVE_OS_CREATEFILE Wait
|
|
02:03 | |
|
Demo: PREEMPTIVE_OS_CREATEFILE Wait
|
|
03:07 | |
|
PREEMPTIVE_OS_WRITEFILEGATHER Wait
|
|
02:06 | |
|
PREEMPTIVE_OS_WAITFORSINGLEOBJECT Wait
|
|
01:36 | |
|
BACKUPXX Waits
|
|
01:28 | |
|
Demo: BACKUPXX Waits
|
|
01:57 | |
|
DBMIRRORXX Waits
|
|
01:51 | |
|
HADR_XX Waits
|
|
02:18 | |
|
TRACEWRITE and SQLTRACE_XX Waits
|
|
01:27 | |
|
LATCH_XX Waits
|
|
01:25 | |
|
ACCESS_METHODS_XX Latches
|
|
02:16 | |
|
Demo: ACCESS_METHODS_XX Latches
|
|
03:00 | |
|
FGCB_ADD_REMOVE Latch
|
|
02:21 | |
|
Demo: FGCB_ADD_REMOVE Latches
|
|
07:59 | |
|
DBCC_XX Latches
|
|
01:41 | |
|
Demo: DBCC_XX Latches
|
|
01:12 | |
|
Miscellaneous Wait Types
|
|
01:57 | |
|
More Miscellaneous Wait Types
|
|
02:03 | |
|
Miscellaneous Latch Classes
|
|
01:55 | |
|
Miscellaneous Spinlocks
|
|
01:59 | |
|
Summary
|
|
01:08 | |
|
Summary |
|
00:20:54 | |
|
Introduction
|
|
00:47 | |
|
Waits, Latches and Spinlocks
|
|
01:17 | |
|
Methodology: No Historical Data
|
|
02:35 | |
|
Methodology: Historical Data
|
|
02:07 | |
|
Real-World Example: Symptoms
|
|
02:08 | |
|
Real-World Example: Analysis
|
|
02:12 | |
|
Real-World Example: Root-Causes
|
|
03:07 | |
|
Real-World Example: Solution
|
|
02:02 | |
|
Resources
|
|
01:57 | |
|
Summary
|
|
02:42 |
