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
What you'll learn
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.
Table of contents
- Introduction 1m
- What are Threads? 2m
- Parallelism 2m
- Parallel Threads Example 1m
- Controlling Parallelism 2m
- Thread Scheduling 2m
- Components of a Scheduler 1m
- Schedulers in SQL Server 2m
- Demo: sys.dm_os_schedulers 2m
- Thread States 1m
- Transition from RUNNING to SUSPENDED 1m
- The Waiter List 1m
- Special Case: Quantum Exhaustion 2m
- Transition from SUSPENDED to RUNNABLE 1m
- The Runnable Queue 2m
- Special Case: Resource Governor 2m
- Resource Governor Example 2m
- Transition from RUNNABLE to RUNNING 1m
- Putting It All Together 2m
- Demo: Examining Threads on Schedulers 3m
- Summary 2m
- Introduction 1m
- What are Waits? 2m
- What are Queues? 2m
- Waits and Queues Methodology 2m
- Wait Times Defined 2m
- Graphical View of Wait Times 1m
- sys.dm_os_waiting_tasks DMV 2m
- Demo: sys.dm_os_waiting_tasks 5m
- sys.dm_os_wait_stats DMV 1m
- Filtering Benign Waits 3m
- Demo: sys.dm_os_wait_stats 5m
- Storing Wait Statistics 2m
- Clearing Wait Statistics 1m
- Demo: Clearing Wait Statistics 4m
- Using Extended Events 3m
- Demo: Using Extended Events 6m
- Summary 1m
- Introduction 1m
- What are Latches? 3m
- Types of Latches 2m
- B-tree Page Split Example 3m
- Latch Contention 2m
- Tempdb Latch Contention Example 3m
- sys.dm_os_latch_stats DMV 1m
- Demo: sys.dm_os_latch_stats 2m
- What are Spinlocks? 2m
- Spinlock Internals 2m
- Spinlock Contention 2m
- sys.dm_os_spinlock_stats DMV 1m
- Demo: sys.dm_os_spinlock_stats 3m
- Clearing Latch and Spinlock Statistics 1m
- Using Extended Events 1m
- Transaction Log Example 1m
- Summary 1m
- Introduction 1m
- What is Relevant? 1m
- Top Wait Types Worldwide Survey 1m
- CXPACKET Wait Explanation 2m
- CXPACKET Wait Example of Non-Skewed Scan 1m
- CXPACKET Wait Example of Skewed Scan 2m
- CXPACKET Wait Solutions 3m
- Demo: CXPACKET Waits 7m
- PAGEIOLATCH_XX Wait 3m
- PAGEIOLATCH_XX Wait Solutions 2m
- Demo: PAGEIOLATCH_XX Waits 2m
- ASYNC_NETWORK_IO Wait 3m
- Demo: ASYNC_NETWORK_IO Waits 2m
- WRITELOG Wait 3m
- WRITELOG Wait Solutions 2m
- Demo: WRITELOG Waits 6m
- PAGELATCH_XX Wait 2m
- PAGELATCH_XX Wait Solutions 2m
- PAGELATCH_XX Wait Solutions (part 2) 2m
- Demo: PAGELATCH_XX Waits 3m
- LCK_M_XX Wait 2m
- LCK_M_XX Wait Solutions 3m
- Demo: LCK_M_XX Waits 6m
- SOS_SCHEDULER_YIELD Wait 3m
- SOS_SCHEDULER_YIELD Wait Solutions 1m
- Using Extended Events to Examine Call Stacks 2m
- Demo: Examining Call Stacks with Extended Events 6m
- OLEDB Wait 1m
- PREEMPTIVE_OS_XX Waits 2m
- PREEMPTIVE_OS_CREATEFILE Wait 2m
- Demo: PREEMPTIVE_OS_CREATEFILE Wait 3m
- PREEMPTIVE_OS_WRITEFILEGATHER Wait 2m
- PREEMPTIVE_OS_WAITFORSINGLEOBJECT Wait 2m
- BACKUPXX Waits 1m
- Demo: BACKUPXX Waits 2m
- DBMIRRORXX Waits 2m
- HADR_XX Waits 2m
- TRACEWRITE and SQLTRACE_XX Waits 1m
- LATCH_XX Waits 1m
- ACCESS_METHODS_XX Latches 2m
- Demo: ACCESS_METHODS_XX Latches 3m
- FGCB_ADD_REMOVE Latch 2m
- Demo: FGCB_ADD_REMOVE Latches 8m
- DBCC_XX Latches 2m
- Demo: DBCC_XX Latches 1m
- Miscellaneous Wait Types 2m
- More Miscellaneous Wait Types 2m
- Miscellaneous Latch Classes 2m
- Miscellaneous Spinlocks 2m
- Summary 1m
About the author
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. Since leaving Microsoft, Paul has been a full-time consultant and trainer on SQL Server around the world, including authoring and teaching the prestigious Microsoft Certified Mast... more