patternsqlMinor
Are the cost percentages in this SQL Server plan over 100% for a valid reason?
Viewed 0 times
thisthearesqlplan100reasonforvalidserver
Problem
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?
Why are many of the costs listed above 100% ? Shouldn't that be impossible?
Solution
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
For example, your query plan shows
The cost percentage = my
EstimatedTotalSubtreeCost / parent node's EstimatedTotalSubtreeCostFor 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` popupCode Snippets
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` popupContext
StackExchange Database Administrators Q#39684, answer score: 3
Revisions (0)
No revisions yet.