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.
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.
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.
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..
Get the query. Get the actual execution plan. Look at which operations run the longest.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.