Bad Names
When we talk about finding queries to tune, there’s an unfortunate term that gets thrown around: Expensive Queries.
Why is it unfortunate? Well, it reinforces the wrong mindset when it comes to query tuning, and leads people down the wrong path, looking at the wrong metrics.
SQL Server does have a cost-based optimizer, but those costs don’t mean anything to your hardware, or even to how long a query runs for.
Those costs are all estimates, based on two-decade old computer specs. There are many times when they’re wrong, or not properly aligned with reality.
Worse, it leads people to want to do crazy things like sort the plan cache by query cost to find things to tune.
Worse than that, they’ll look at “query cost relative to batch” to compare two queries for efficiency.
Ethically Sourced
There are many sources to find queries eating up your server hardware.
- Plan cache
- Query Store
- Extended Events
- What’s currently running
- Monitoring tools
The point of this post isn’t to teach you how to use any of those things, but to teach you how to be smarter about using them in whatever way you’re comfortable.
My two favorite metrics to look at when looking for queries to tune are CPU and RAM. I explain why in the post, but the short story is that they’re reliable, real-life metrics that can be directly measured before and after to gauge progress.
I don’t look at things like reads, because those might go up or down while your query runtime doesn’t change at all.
They’re also pretty misleading if you’re looking at STATISTICS IO in a lot of circumstances, like with lookups.
Sortie
A while back I recorded a bunch of videos that show how cached/estimated plans can be really misleading when it comes to costs and all that stuff.
You can find it here:
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.
Yes, yes, yes… and yes!
Slow is my other favorite term. “Let’s just find the slow queries, what’s so hard about that?”
Maybe people are motivated by the idea of saving money on their expensive queries ðŸ¤
If you want to go down that road, an appointment with managers is still the best thing to do when it comes to deadlock issues. Every time, everywhere they look at you as if someone died horribly right there in their servers. In their minds, this is probably the worst case scenario.