HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

Submitted by: @import:stackexchange-dba··
0
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?

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 EstimatedTotalSubtreeCost / parent node's EstimatedTotalSubtreeCost

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

Code 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` popup

Context

StackExchange Database Administrators Q#39684, answer score: 3

Revisions (0)

No revisions yet.