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
In this query plan:
https://www.brentozar.com/pastetheplan/?id=HydtQjsO5
...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?
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).
Campbell
SQL Development
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.
