Further From Truth
Estimated plans can be a bit confounding, huh?
Whether you see one by hitting CTRL+L, looking in the plan cache, or looking in Query Store, the song remains the same.
The query is slow: sometimes? always? usually? rarely?
But where? But why?
Estimated plans can hide a lot of work from you. They’re sort of like government contracts, where the optimizer is the government: it picks the cheapest plan, but then…
(Okay, maybe it picks a plan because a friend or relative or big donor was behind it. Sometimes it seems that way, with the nonsense you end up with.)
Of course once execution is under way, you run into all sorts of unexpected costs: Everything is much more expensive, and takes a whole lot longer than projected.
Glad Ways
From a practical perspective, estimated plans can be useful to get a general idea of the plan shape that the optimizer is coming up with.
Despite all the lying estimated plans can do, you may be able to spot some obvious anti-patterns.
Of more utility is when the query is particularly long running, or when the query will modify underlying data.
When I’m tuning modification queries, there’s usually some pain involved when you need to reset things.
For example:
- Most people don’t want to wait 5-10 minutes just to see if a plan changed after making changes
- Deleting data that doesn’t exist
- Updates that don’t change anything
- Not being able to insert duplicate data a second time
…Are all hindrances to getting things done.
And so you need to restore or roll things back another way (and boy howdy has SQL Server 2019 spoiled me for rollbacks).
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.
?