I wanted to blog about SQL Server Wait Stats today, but from a bit of a different angle than your normal Wait Stat blog.
I often find myself being shown TOP X wait stats output and being told “OMG, these are horrible, we need to fix these!!!” And I stop them, counter with a “no, not necessarily, let’s dig deeper. Are these waits abnormal?” “Abnormal? SQL Server has waits, that’s always bad, right?”
If you’re already familiar with SQL Server Waits, you will know that SQL Server is always waiting on something. And that’s perfectly okay. A few months ago, I was driving home from SQL Sat Cleveland, and came up with a fantastic analogy that I thought would describe these waits rather well.
Pretend two people, Vanessa and Wade, are on a date and having a conversation. Vanessa says something, then Wade responds, and their conversation continues back and forth. But between each person’s statement, there is a very brief moment of silence. That can be thought of as a “wait” – the other individual is processing the prior message and formulating a response. And in the context of this conversation, these waits are perfectly normal.
Now, let’s pretend that Wade says something really, really stupid. It actually shocks Vanessa, who is speechless. Instead of responding immediately, she waivers and remains silent. A long period of time passes before she responds to Wade. This particular wait is abnormal! And so goes a SQL Server wait.
Okay Andy, does that mean that if my SQL Server waits all have long timeperiods, that those are all of the bad ones? No, not necessarily either. In the example of Vanessa and Wade, yes, because their normal cadence of conversation is very rapid with only brief waits. But let’s look at a second example.
Pretend you have two grumpy old men, Jack and Walter, fishing out on a lake in a boat. It’s quiet and they’re grumpy, so they’re not saying much. Walter might make a comment about the fish not biting, and Jack may respond after a lengthy period of time with a brief response. After a long period of silence, Jack may comment about his beer being empty, and he asks Walter for another. Walter fails to respond, because he’s dozed off. And only after several minutes does Jack actually turn around to see that Walter has dozed off, and gets a beer for himself.
If we take a step back, think of each couple as a server’s workload. What does your workload look like normally? It is difficult to know if a given SQL Server Wait time period is anormal if we don’t know what our times look like under normal circumstances. This is why many stress the importance of baselining your SQL Servers.
So the next time you find yourself explaining SQL Server Waits to someone new, feel free to use this analogy to help illustrate why waits in of themselves are perfectly normal, and what a good wait vs a bad wait may look like.