Guest Star
There’s a lot of confusion about what costs mean in query plans. Often when working with clients, they’ll get all worked up about the cost of a plan, or an operator in a plan.
Things I hear over and over again:
- It’s how long the query executed for (plan cost)
- It’s the percent of time within a plan an operator executed for (operator cost)
Neither of those things are true, of course.
The optimizer doesn’t know that your storage is maybe awesome. It assumes that it’s not. Ever seen how high random I/O is costed?
And no matter how much memory you have, or how much of your data is already in memory, it starts with the assumption that none of it is (cold cache).
Costs can be especially misleading in estimated/cached plans when parameter sniffing is to blame.
What Are Costs Good For?
For me, I mostly used costs to show why SQL Server may have chosen one plan over another. The thing is, once you understand that the optimizer chooses plans based on cost, it’s easy to make the logical leap that… the other option was estimated to be more expensive.
Another thing is that while many metrics have “estimated” and “actual” components when you collect an actual execution plan…
… None of those estimated cost metrics have actual components that appear in actual plans, nor do they get updated after a query runs to reflect what happened when it ran.
If they did that, they’d be useless to illustrate the one point they can reasonably make: why a plan got chosen.
Better Metrics
In more recent versions of SQL Server and SSMS, you get operator times. For more detail on timing stuff, check out my videos here and here on it.
Along with operator times, we get information about I/O, row/thread distribution in parallel plans, and a bunch of other useful metrics.
I’d much rather see either the last runtime for operators or the average runtime for operators in a plan. Before you go calling me crazy, remember that SQL Server 2019 has the a new DMV called sys.dm_exec_query_plan_stats that tracks the last known actual execution plan for a query.
Long term, it makes way more sense to replace costs with operator runtimes. That would make finding the worst parts of query plans a lot easier.
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.
There should be database-level settings available that allow adjustment of cost calculation parameters as appropriate to the current environment. Got fast storage? Great! Adjust the “io_cost_weighting” parameter down. Even better, at startup maybe let SQL Server run a few tests and adjust the cost calculation parameters automatically based on the current environment. Costs shouldn’t be calculated based on the performance of a Microsoft developer’s computer back in the 1990s.
There are a couple undocumented DBCC commands: SETIOWEIGHT and SETCPUWEIGHT that let you tinker with stuff like that, but you know…
Agreed, the costs and especially their percentages shown visually inside the plan, and across plans in a batch, are just confusing for many devs and junior DBAs, and more often than not they’re red herrings. I always tell people to disregard them and run everything through Plan Explorer, although that’s not always possible of course. I’d definitely be happy to see useful actual metrics as the lead numbers in the plan, and the costs hidden away somewhere inside for deeper troubleshooting if needed.
Yeah, maybe removing them is a bit harsh ?
I think you’re right, that going the route of putting more useful numbers front and center is the smarter one.