Understand Your Plan: Costs (And Why You Shouldn’t Bother With Them)

Posted on by Erik Darling

Goodfor

Costs are okay for figuring out why SQL Server’s cost-based optimizer:

  • Chose a particular query plan
  • Chose a particular operator

Costs are not okay for figuring out:

  • Which queries are the slowest
  • Which queries you should tune first
  • Which missing index requests are the most important
  • Which part of a query plan was the slowest

But a lot of you believe in memes like this, which leads to my ongoing employment, so I’m not gonna try to wrestle you too hard on this.

Keep on shooting those “high cost” queries down that are part of some overnight process no one cares about while the rest of your server burns down.

I’ll wait.

Badfor

In a lot of the query tuning work I do, plan and operator costs don’t accurately reflect what’s a problem, or what’s the slowest.

Here’s an example, from a fairly large query plan, where the operator times show nearly all the execution time in a branch full of operators where the costs aren’t particularly high.

The plan runs for ~5 seconds in total.

SQL Server Query Plan
onion

Would you suspect this branch is where ~4 of those seconds is spent? What are you gonna tune with an index seek? You people love seeks.

I’ll wait.

Time Spent

Where queries spend the most time in a plan is where you need to focus your query tuning efforts. Stop wasting time with things like costs and reads and whatnot.

If you want a good list of things not to do while troubleshooting a query, start with the bullet points in this Microsoft support blog:

  • Update the statistics and rebuild the indexes.

  • Use Recompile or MAXDOP, grant memory hint

  • Use the latest compatibility model

  • Clear buffer pool.

  • Clear procedure cache.

  • Comparing the same automatic statistics, comparing rows, etc..

  • Plan guides.

Get the query. Get the actual execution plan. Look at which operations run the longest.

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.

Related Posts

7 thoughts on “Understand Your Plan: Costs (And Why You Shouldn’t Bother With Them)”

  1. Should we put a check mark on all the bullets before getting things done right? To pave the way, you know.

  2. Pingback: The Cost is a Lie – Curated SQL

  3. Is that a fair comparison though, as it seems that the Microsoft example was comparing poor performance of the same query between an on-premise server vs azure hosted. They were trying to figure why one host behaved different to the other.

    I’m not disagreeing that they aren’t the steps I’d use to performance tune a query in general though.

    Hell, half the time when I’m diagnosing, I end up simplifying the query before adding things back to see where it’s gone wrong – because even with tooling, and SentryOne Plan Explorer showing me Cumulative costs of a branch, I’m just not the greatest at figuring why something behaves the way it does.

    I would be interested in how you solved your problem. I may be blind, but it looks like two branches, not one. I am assuming the bottom is one of the branches off the nested-loop? I’m just not sure where there is a saving there – short of why two aggregations (due to parallelism, or nested aggregate query?)

    1. Er… so if hopped on a call with Microsoft support, and they tried to use MAXDOP to force a parallel query, would you feel confident?

      What if they wanted you to clear the plan cache and drop clean buffers on a production server?

      Big yikes over here.

  4. Hello,
    It was very useful blog “Understand Your Plan: Cost”, it was describing all the necessary key lists.
    I have a question regarding one of my Stored procedure which runs fine for few days and then i days it’s runs for longer time then again runs fine for few days.
    It’s like 4 days running fine under 30 minutes and then next day runs longer almost 3-5 hours, that happening within cycle like 3-5 days runs as expected under 30 minutes then 1 day runs for almost 3-5 hours.
    Is it above steps will help?
    My stored proc has few deleted and updates with couple Insert statements which includes 3 different database tables but only couple joins which is also joins table variable Table which I have couple table variable table declared in beginning.

    Thanks in advance for your tips!

Comments are closed.