Shouldn't Operator cost at least be as large as I/O or CPU cost that comprises it?

I have a query on one server which the optimizer estimates will have cost of 0.01. In reality it ends up running very badly.

• it ends up performing a clustered index scan

Note: You can find the nitty-gritty ddl, sql, tables, etc here on Stackoverflow. But that information, while interesting, is not important here - which is an unrelated question. And this question doesn't even need DDL.

If I force the use of a covering index seek, it estimates the use of that index will have a subtree cost of 0.04.

• clustered index scan: 0.01

• covering index scan: 0.04

So it's hardly surprising that the server would choose to use the plan that:

• actually causes 147,000 logical reads of the clustered index

• rather than the much faster 16 reads of a covering index

Server A:

| Plan                                       | Cost      | I/O Cost    | CPU Cost  |
|--------------------------------------------|-----------|-------------|-----------|
| clustered index scan (optimizer preferred) | 0.0106035 | 116.574     | 5.01949   | Actually run extraordinarily terrible (147k logical reads, 27 seconds)
| covering index seek (force hint)           | 0.048894  |   0.0305324 | 0.0183616 | actually runs very fast (16 logical reads, instant)

This is with statistics up-to-date WITH FULLSCAN no less.

Try on another server

So I try on another server. I get estimates of the same query, with a recent copy of the production database, also with statistics up to date (WITH FULLSCAN).

• This other server is also SQL Server 2014

• but it correctly realizes that clustered index scans are bad

• and it naturally prefers the covering index seek (because the cost is 5 orders of magnitude lower!)

Server B:

```
| Plan | Cost | I/O Cost | CPU Cost |
|-------------------------------------------|-------------|------------|-----------|
| Clustered index scan (force hint) | 115.661 | 110.889 | 4.77115 | Runs extraordinari

Row Goals

If a row goal gets set in the query, this can affect row estimates and costing.

You could confirm if this is causing the problem by running the query with trace flag 4138 enabled (which will remove the influence of the row goal).

Buffer Pool Size

The estimated cost for some I/O operations can be reduced if there's a larger buffer pool available (the server with reduced cost has 14 GB of RAM, vs 6 GB on the other machine).

You can check for the influence of this behavior by looking for "EstimatedPagesCached" in the plan XML. A higher value for this property could reduce the I/O cost of parts of the execution plan that potentially access the same data.

Available Schedulers

For a parallel query, the CPU cost of an operator can be reduced by as much as "# of schedulers / 2." You can check what value this has by looking for "EstimatedAvailableDegreeOfParallelism" in the plan XML.

I mention this because I noticed that the "slow query" ran on a server with 4 cores, while the faster one ran on a server with 1 core.

Costs Are Weird and Broken

Forrest talks about a bunch of different ways that costs can end up not making sense on his blog: Percentage Non Grata

StackExchange Database Administrators Q#250474, answer score: 4