- Course
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
Intermediate
- Course
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
Intermediate
Get started today
Access this course and other top-rated tech content with one of our business plans.
Try this course for free
Access this course and other top-rated tech content with one of our individual plans.
This course is included in the libraries shown below:
- Data
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.
SQL Server: Performance Troubleshooting Using Wait Statistics
Intermediate
Table of contents
-
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 | 53s
-
Schedulers in SQL Server | 1m 52s
-
Demo: sys.dm_os_schedulers | 2m 8s
-
Thread States | 1m 22s
-
Transition from RUNNING to SUSPENDED | 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 | 47s
-
Putting It All Together | 1m 31s
-
Demo: Examining Threads on Schedulers | 3m 19s
-
Summary | 1m 37s
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.