SQL Server: Performance Troubleshooting Using Wait Statistics

Learn how to begin troubleshooting SQL Server performance problems, applicable for developers and DBAs from SQL Server 2005 onwards
More
Learn how to begin troubleshooting SQL Server performance problems, applicable for developers and DBAs from SQL Server 2005 onwards
More
Introduction
15m 9s
Waits
43m 18s
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
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.

Learn by doing. Use hands-on exercise files to follow along with the course and practice concepts as you’re watching them. Sign up now to enhance your training.

Start free trial

Course transcripts are the written form of the course audio. You can use transcripts to just read the audio, or to search for terms or phrases to find where they are discussed in the training. Sign up to try them now.

Start free trial

Ask questions, read feedback from other learners and share your input, all in our discussion forums. Sign up now to join our community of learners, course authors and friendly support staff.

Start free trial

You’ve completed the course but did you master it? Test yourself with questions that gauge your understanding of the material and show you what to brush up on. Sign up to get started.

Start free trial
Table of Contents
Introduction
15m 9s
Waits
43m 18s
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
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.
Exercise Files

Learn by doing. Use hands-on exercise files to follow along with the course and practice concepts as you’re watching them. Sign up now to enhance your training.

Start free trial
Transcript

Course transcripts are the written form of the course audio. You can use transcripts to just read the audio, or to search for terms or phrases to find where they are discussed in the training. Sign up to try them now.

Start free trial
Discussion

Ask questions, read feedback from other learners and share your input, all in our discussion forums. Sign up now to join our community of learners, course authors and friendly support staff.

Start free trial
Knowledge Check

You’ve completed the course but did you master it? Test yourself with questions that gauge your understanding of the material and show you what to brush up on. Sign up to get started.

Start free trial
Course info
Level
Intermediate
Rating
(393)
Duration
4h 31m
Released
28 Jun 2012
Course authors

Paul is the CEO of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Among other things, he wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development.

Share course