Going For Distance
There are waits, and then there are waits. If you know, you know, you know?
If you don’t know, here’s the deal: there are some waits that are gonna happen, and you’re not gonna be able to do much about them.
In this post, I’d rather introduce you to waits that you should pay attention to and can fix, and how they relate.
This isn’t meant to cover every single scenario, but it’s a good starting place. For example, with cloud and VM workloads being more common than ever, you may need to look at different settings or configurations outside of SQL Server.
This also goes for those of you using SANs, where everything from the networking to the controllers to the disk arrays can potentially be a bottleneck.
If one looks hard enough, one may find inefficiencies anywhere.
Going For Speed
The waits I generally feel are common enough and fixable enough are below.
There are plenty of less common waits with perfectly fine solutions, but let’s get you started with the ones that tend to drift up towards the top of all those magical scripts you find strewn about the internet.
- CXPACKET/CXCONSUMER: Parallelism
- LCK_*: Locking
- PAGEIOLATCH_*: Reading pages from disk
- RESOURCE_SEMAPHORE: Running out of memory to grant queries
- THREADPOOL: Running out of worker threads
Of course, these waits can be closely related, can’t they?
- Many concurrent parallel queries can lead to running out of worker threads
- A common way to have many parallel queries stack up is to have them get blocked
- Granting lots of memory to queries can mean less room for the buffer pool and more reading from disk
Of course, it’s really when these waits happen that matters, isn’t it? If there’s no end-user impact, who cares?
Always Be Checking
For the billionth time, if you have servers you care about performance for, get a monitoring tool.
If your boss won’t spring for one, try a free one. Or if you’re feeling really saucy, download a free trial of a paid tool.
Once you know which waits are happening when users might care, you stand a better chance at being able to start fixing things.
But I don’t want you to think you always need wait stats to tune things. Often, waits for a single query don’t matter. Waits really matter when you want to judge a workload.
Of course, there are exceptions. If every 2nd or 3rd time you run a query, it spends 20 seconds reading pages from disk, but the rest of the time it takes 2 seconds, it’s probably not the query (assuming everything in the plan is identical).
Tomorrow we’ll dig into CPU related waits, and how to address those.
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.