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.
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!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.