Expensive Operator (rule 35): fires on queries that run for a few milliseconds

[erikdarlingdata]

Problem

Rule 35 adds an Expensive Operator warning when one operator's own time is 20% or more of the statement's elapsed time. At 50% or more, the warning is Critical. The rule has no minimum time. So a query that runs for a few milliseconds gets the warning too, often as Critical.

Examples from the test fixtures and from plans captured on SQL Server 2022:

Plan	Statement elapsed	Warning
multi_index_update_plan.sqlplan, a one-row UPDATE	1 ms	Critical: Clustered Index Update took 1ms (100.0% of statement elapsed)
join_or_expression_plan.sqlplan	3 ms	Nested Loops took 1ms (33.3%), and Sort took 1ms (33.3%)
join_or_mixed_parameter_plan.sqlplan	3 ms	The same two warnings
WHERE t.V LIKE @a OR t.V LIKE @b on an indexed column	12 ms	Critical, "up to 100% benefit": Index Seek took 12ms (100.0% of statement elapsed)

In a short query, one or two operators usually take most of the time. So the warning sends the user to look for a problem in a query that has none. planview analyze also counts the warning in its summary of critical warnings.

Decision needed

The rule needs a minimum. The value is a design decision. Two options:

• A minimum elapsed time for the statement, for example 100 ms. Below it, the rule does not fire.
• A minimum own time for the operator, in milliseconds, in addition to the 20% share.

The rule came from item C8 of #215, which asked for expensive operators to show even when no other rule has advice.

Found while working on #558.