The People Who Stare At Plans
I’m going to be honest with you, dear readers. I’ve fallen out of like with wait stats.
Unless there’s a massive resource or blocking problem, they’re a bit bland.
Tuning a single query, I don’t look at them at all.
Sure, they can be a good detail when you’re painting an overall picture of a server, but they’re misleading a lot.
They could be more useful, if:
- You knew when they happened
- You knew which queries caused them
- You knew which queries were impacted by them
Let’s say I look at your server. Let’s say I run any script that analyzes wait stats.
Even Your Worst
Holy cow, you sure have a lot of (THREADPOOL, RESOURCE_SEMAPHORE) waits here, pal.
You got a big problem with hardware.
Well, maybe.
Let’s say they happen at night, when no one’s around.
We could sit there all day hitting F5 and never see a sign of the problem.
It sure would be nice to know when they happened.
It Was Really Something
Not everyone’s server is a 24/7 performance nightmare.
Lots of people have servers that get steadily used from 9-5, but don’t really have spikes normally.
Except at the beginning or end of the month/year/quarter.
If you look at wait stats in general, the server might look very bored most of the time.
All those sums and averages smooth out over time.
Most people don’t always have a heart rate of 180 bpm, but they might for the 5 minutes they have a heart attack.
That’s sort of important.
The Sanest Days Are Mad
What if we see a lot of long lock waits?
I bet users are really mad about those.
Have you heard about our Lord and Savior, RCSI?
You’re definitely gonna need that.
Which database?
Oh, that logging database that users never query.
Darn.
Again, knowing which queries were on the giving and receiving end of that would be stellar.
Toolio, My Toolio
It’s not that wait stats themselves are dull, it’s the way we’re forced to look at them today.
You either have to hit F5 at the magic moment to catch them, log them on your own, or buck up for a monitoring tool.
It’s a matter of how and what, and when data gets logged for them, not a matter of tool quality.
I’d love to see a more complete picture of these things when trying to diagnose or troubleshoot issues.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
“Have you heard about our Lord and Savior, RCSI?” Must have this on a shirt RFN
I’d buy one!