Sort execution plan XML by estimated operator cost

Question

I want to sort execution plan XML by Estimated Operator Cost, but the problem is:

SQL Server execution plan has the "Estimated Operator Cost" entry in UI:

...but that value is missing from the XML output.

There are several suggestions on how to sort the XML, like:

• SQL Server – Find the most expensive operations in Execution plans by Mladen Prajdić.
• How to find the most expensive operation in an execution plan on the old MSDN forums.

But neither are even close to what I'm having, as the "Estimated Operator Cost" seems to be a super complicated calculation.

Is there any solution to this problem please?

Answer 1

nope

I'm not sure why you'd want to do this, since cost is always an estimate, and quite prone to being incorrect as far as what the most time consuming portion of a query is.

At this point, cost is considered a unitless metric -- you may come across the phrase query bucks in your travels -- there's no "actual cost" even in actual query plans.

If your plan is to dig through the plan cache for comparatively expensive operators, your better bet is to sort plans by a more useful metric like average cpu. That at least gives you some idea what ran for a long time.

If you just want to examine one query, your best bet is to get the actual execution plan and look at operator times to figure out which part ran slowly.

That can get annoying with larger stored procs, which may be easier to observe with Extended Events, or just look at plans for specific queries in, if you have some idea which ones are running poorly.