All we need is an easy explanation of the problem, so here it is.
In this query plan:
…multiple nodes have a cost of 100%. How does this happen? How do you figure out where to start optimizing when so many nodes have what must be an erroneous cost? Is there something else to use instead of the cost percent?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Cost is based on estimates and it’s often when these estimates go wrong that you get a slow plan – so it’s not a great idea to base your tuning targeting on them.
Use the actual elapsed time. This is collected for you when you use the Include actual execution plan button in SSMS. You may need to look at the XML output to see the timings depending on your SSMS version.
…multiple nodes have a cost of 100%
This was explained by Microsoft in response to a bug report:
Thanks for taking the time to file this observation and simple repro. The strange cost percentage values that you observed are an aritifact of the specific structure of the query plan that are a bit confusing but ultimately do make sense. They will not adversely affect the running of the query in any way.
The concatenation operator has two children – the table scans. However the server expects that it will not be necessary to execute the second table scan. This is because it expects to find a row from the first table scan which would satisfy the "exists" part of the query. Therefore it does not include the cost of the second table scan when computing the total cost of the subtree rooted at the concatenation. However it still estimates and reports the cost of the second table scan, which the management tool the computes as a percentage of the total query cost as if it will be executed.
In this case of course, it would be necessary to run the second table scan since the first is empty. However the engine operates on the conservative assumption that there will always be at least one row (which may only have been added after the query was compiled).
Is there something else to use instead of the cost percent?
Estimated cost is used by the optimizer to choose between different plan operators in the context of its cost model. Your system is very unlikely to have the same physical performance characteristics as the model.
There are many different metrics important in query tuning, depending on your goals. You might choose to take account of some or all of physical reads, memory consumption, CPU usage, and elapsed time in forming a complete opinion. My personal primary metric is CPU time in most cases.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂