I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:
Why are many of the costs listed above 100% ? Shouldn't that be impossible?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
I'd been also curious about why sometimes some cost is displayed as 100%, 200%, 300%...and even more. After analyse xml file of query plan, i got it.
The cost percentage = my
EstimatedTotalSubtreeCost / parent node's
For example, your query plan shows
Clustered Index Insert take cost 914%, to understand how it calculate this percentage,
1. Move mouse to `Clustered Index Insert` to show popup, you can see the cost `EstimatedTotalSubtreeCost`, e.g, 0.2
2. Move mouse to this node's parent node `COND WITH QUERY`, check the popup to get `EstimatedTotalSubtreeCost`, e.g, 0.0218818
3. Calculate 0.2/0.0218818 = 914%, this is the cost percentage displayed in `Clustered Index Insert` popup