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
Course info
Rating
(429)
Level
Intermediate
Updated
Jun 28, 2012
Duration
4h 31m
Table of contents
Introduction
SQL Server Threading Model
Waits
Latches and Spinlocks
Troubleshooting Patterns
Summary
Description
Course info
Rating
(429)
Level
Intermediate
Updated
Jun 28, 2012
Duration
4h 31m
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.

About the author
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.

More from the author
More courses by Paul Randal
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Paul Randall: Hi, this is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This course is about performance troubleshooting in SQL Server using wait statistics, and this is Module 1, the introduction. First of all, everything in this course is applicable to all versions of SQL Server, from SQL Server 2005 onwards, all the way up to SQL Server 2012. If there's anything that's not applicable to certain versions I'll call that out as we go through the class. So I'm sure all of you have had requests to troubleshoot performance of SQL Server. Somebody walks up to you and says something like, you know, it feels like the server is running slower than it was last week. I can't actually pinpoint where the problem is, but it just feels like it's running slower. How do you troubleshoot this? Somebody else comes up and says, you know, things are actually running slower, we can tell things are running slower, and we think it's the database. Then, of course, there's the classic, you know, things are running slower, but nothing changed, we didn't change anything, but we know things are running slower. How do you troubleshoot that stuff? Where do you start troubleshooting when somebody comes up and says there's poor performance on SQL Server? There are a whole bunch of different places where you could start. You could start by looking at hardware configurations, is there something wrong with memory? Is there something wrong with, for instance, the I/O subsystem or paths to the I/O subsystem? Is there something wrong with the indexing strategies? Is there fragmentation? Is there indexes that aren't being used? Is there something wrong with the application code? Is there problems with the way that people are running queries, for instance? A whole bunch of different places you could start. But without some clear direction of where to start looking what I find many people do when they're trying to investigate performance problems is they flail around and they waste a whole bunch of time. What I'm going to show you is how you can avoid that.

SQL Server Threading Model
Hi, this is Paul Randal from SQLskills. com. And I'm recording this course for Pluralsight. This course is about performance troubleshooting in SQL server using wait statistics, and this is module 2, the SQL Server Threading Model. Once you get to know SQL server, you'll figure out that it really is a kind of mini operating system within Windows itself. It's a very large complicated server product and there's a lot of things that although Windows could do it for SQL server, it's more efficient for SQL server to do it for itself. For instance, it does its own memory management. So it gets memory from Windows and then it's going to parcel it out to threads within SQL server for whatever use that it needs. It manages its own IO, whether that's synchronous or asynchronous IO, for data files, log files, backups, it does it all itself. Obviously, it's using Windows under the covers but it's controlling what's happening itself. Similarly, for scheduling, for working out when a particular thread should be scheduled to do work and when it has to wait for a resource. It does all that stuff itself. In this module then, we're going to cover how thread scheduling works. We're going to look at threads and discover what those are. I'll talk about basic scheduling and how that works. And then we'll look at the scheduling states with Inside SQL server and how threads transition from state to state. We'll also touch on a few special cases as we go along.

Waits
Hi, this is Paul Randal from SQLskills. com and I'm recording this course for Pluralsight. This course is about Performance Troubleshooting in SQL Server Using Wait Statistics. And this is Module 3 where we're going to talk about Waits. Now that we've talked about threading and thread scheduling and how that works inside SQL Server, and before we're going to use wait statistics themselves for performance troubleshooting, we're going to have to define what wait statistics mean. So in this module, we're going to talk about what waits are and what queues are. Remember that the overall methodology is called the waits and queues performance tuning methodology. We're going to look at what the various wait times are as threads move from state to state inside the scheduler. We're also going to look at the two DMVs you can use to get wait statistics information out of SQL Server, sys. dm_os_wait_stats and sys. dm_os_waiting_tasks. Now, the information that comes out of those isn't that useful until you manipulate it a little bit to make it actionable. So we're going to look at some scripts that you can use to do that. Finally, we'll look at using the Extended Events feature to be able to view wait statistics maybe for a single connection to SQL Server, for instance.

Latches and Spinlocks
Hi this is Paul Randal from SQLskills. com and I'm recording this course for Pluralsight. This course is about performance troubleshooting in SQL server using wait statistics. And this is Module 4 where we're going to talk about latches and spinlocks. We've already talked about wait statistics and how you can use wait statistics to get a lot of information about what's happening inside SQL server and this can be really useful when you're doing performance troubleshooting. Sometimes, though, wait statistics are not enough and we have to go a little bit deeper and look at latches and spinlocks. This is more advanced analysis but you might have to do that sometimes to get to the root cause of a performance problem especially when wait statistics doesn't show you exactly what's going on or when the wait that's occurring for instance is a LATCH underscore EX or a LATCH underscore SHARE wait. We have to figure out what that latch is. So in this module, we're going to talk about latches and latch statistics, spinlocks and spinlock statistics and the DMVs you can use to examine what's going on with latches and spinlocks. I'll also walk you through some examples of latch and spinlock contention.

Troubleshooting Patterns
Hi! This Paul Randal from SQLskills. com and I'm recording this course for Pluralsight. This course is about performance troubleshoot in SQL Server using Wait Statistics, and this is module 5, where we're going to talk about troubleshoot patents that you'll see when you're troubleshooting performance problems. Now that we've talked about the threading model in SQL Server and also what waits are and latches and spinlocks, it's necessary to talk about different wait types and understand what the most common wait types are that are linked to performance problems so that you can put these knowledge together and actually use wait statistics analysis for performance troubleshooting. Now, one thing I want to say is that there are many hundreds of wait types and latch classes that could become prevalent in the environment, but there isn't time in this course to talk about every single one. So what I'm going to do is talk about the most common ones in my experience that you're likely to see out there in the field. So in this module, we're going cover common wait types, what they mean, what they don't mean, and how to investigate them further and some potential solutions, some common latch classes with the same, what they mean and so on. And then some miscellaneous waits, latches, and spinlocks that might crop up but aren't worth spending an entire recording talking about.

Summary
Hi, this is Paul Randal from SQLskills. com and I'm recording this course for Pluralsight. This course is about performance troubleshooting in SQL server using wait statistics and this is Module 6, The Summary. So to finish off the course now that we've talked about all the different kinds of wait statistics, what wait statistics are, threading, scheduling and all that kind of good stuff. We're going to talk about the methodology that you're going to use when you do or don't have historical data that you can draw on and I'm going to walk you through a real-world problem that I had from a consulting client where they had a performance troubleshooting issue and the first thing I did was use wait statistics to figure out what was going on and how to drill in to find out what the root causes were and then what the actual things I did were to solve the problems that they had.