11

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 extraordinarily terrible as server A (147k logical reads, 27 seconds)
| Covering index seek (optimizer preferred) |   0.0032831 |   0.003125 | 0.0001581 | Runs fast (16 logical reads, near instant)

What I can't figure out is why for these two servers, with nearly identical copies of the database, both with up-to-date statistics, both SQL Server 2014:

  • one can run the query so correctly
  • the other falls over dead

I know it seems like a classic case of statistics being out of date. Or cached execution plans, or parameter sniffing. But these test queries are both being issued with OPTION(RECOMPILE), e.g.:

SELECT MIN(RowNumber) FROM Transactions
WITH (index=[IX_Transactions_TransactionDate]) WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

If you look closely, it looks like the "operator" estimate is wrong

The clustered index scan is a bad thing. And one of the servers knows it. It's a very expensive operation, and the scan operation should tell me so.

If I force the clustered index scan, and I look at the estimated scan operations on from both servers, something jumps out at me:

enter image description here

| Cost                | Server A    | Server B   |
|---------------------|-------------|------------|
| I/O Cost            | 116.573     | 110.889    |
| CPU Cost            |   5.01945   |   4.77155  |
| Total Operator Cost |   0.0106035 | 115.661    |
                        mistakenly  | avoids it
                          uses it   |

The Operator cost on server A is just way too low.

  • the I/O cost is reasonable
  • the CPU cost is reasonable
  • but taken together, the overall Operator cost is 4 orders of magnitude too low.

That explains why it is mistakenly choosing the poor execution plan; it simply has a bad operator cost. Server B has it correctly figured out, and avoids the clustered index scan.

Isn't operator = cpu+io?

On nearly every execution plan node you will ever hover over, and every screenshot of execution plans on dba, stackoverflow, and every blog ever, you will see that without fail:

operatorCost >= max(cpuCost, ioCost)

And in fact it is usually:

operatorCost = cpuCost + ioCost

So what's going on here?

What can account for the server deciding that costs of 115 + 5 are nearly nothing, and instead decides something 1/10000th that cost?

I know SQL Server has options to adjust the internal weight applied to CPU and I/O operations:

DBCC    TRACEON (3604);     -- Show DBCC output
DBCC    SETCPUWEIGHT(1E0);  -- Default CPU weight
DBCC    SETIOWEIGHT(0.6E0); -- I/O multiplier = 0.6
DBCC    SHOWWEIGHTS;        -- Show the settings

And when you do, the operator cost can end up below the CPU+I/O cost:

enter image description here

But nobody has been playing with those. Is it possible that SQL Server has some automatic weight adjustment based on the environment, or based on some communication with the disk subsystem?

If the server was a virtual machine, using a virtual SCSI disk, connected by a fiber link to a Storage Area Network (SAN), that it might decide that CPU and I/O costs can be ignored?

Except it can't be some permanent environment thing in this server, because every other query that I've found behaves properly:

enter image description here

 I/O:       0.0112613
 CPU:      +0.0001
           =0.0113613 (theoretical)
 Operator:  0.0113613 (actual)

What can account for the server not taking:

I/O Cost + Cpu Cost = Operator Cost

correctly in this one instance?

SQL Server 2014 SP2.

  • 1
    Why in Server A ordered is true, whereas in Server is false? Are DDLs identical? – dbilid Nov 4 '19 at 12:09
  • also, here the estimated cost is also way smaller than CPU+I/O. I guess that sql server for clustered index scan reports the full table CPU and I/O cost, even if it is only a partial scan, and only reports the actual estimate based on how fast it will find the expected value as total cost. I have no access to an instance to test this guess, but in your case, as it assumes independence between row id and date, it assumes that it will find the result really fast, whereas this is not the case, and the result is towards the end of the table – dbilid Nov 4 '19 at 13:09
  • 2
    I know it's been a month since you posted this question.... but if you have actual query plan XML that you could post on pastetheplan.com, that might be helpful for someone. – AMtwo Nov 5 '19 at 0:36
  • I see that the node ID for the compared items is slightly different, so I think there is more changed in the overall plan than just these two items – simon coleman Nov 8 '19 at 11:00
2

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

It depends.

It's a shame that other person deleted their post because I came up with similar ideas.

Row Goals

This is not what you are experiencing based on the screenshots, but this is a factor in the calculation of the Operator cost. I/O and CPU costs do not scale, they will show a per-execution cost if a row goal is not in effect. The Operator cost does scale to show the row goal. This is one instance where I/O and CPU does not exactly comprise the Operator cost, the estimated number of executions is something to take into account. How you view these stats are dependent on if you are looking at the inner or outer input.

Source: Inside the Optimizer: Row Goals In Depth by Paul White - August 18, 2010 (archive)

Buffer Pool Usage

This could be a factor that is affecting you.

The full cost of an operation should be the number executes multiplied by the CPU cost, plus a more involved formula for the number of IO required. The formula for IO represents the probability that an IO will already be in memory after a number of pages have already been accessed. For large tables, it also models the chances that a previously accessed page may have already been evicted when it is needed again. The sub-tree cost represents the cost of the current operation plus all operations that feed into the current operation.

Source: Execution Plan Cost Model by Joe Chang - July 2009 (archive)

Onto your problem

We can see in your screenshots that you have a wildly interesting subtree cost on the server not performing well. What is interesting is that it has more memory to use and less CPU.

The above information indicates to me, you probably have a problem with the Subtree Cost and the Operator cost is a symptom.

...the Estimated Subtree Cost, is the cumulative (added up in NodeID order) costs of each individual operator.

Source: Actual Execution Plan Costs by Grant Fritchey - August 20, 2018 (archive)

I think the answer lies in these sentences:

The formula for IO represents the probability that an IO will already be in memory after a number of pages have already been accessed. For large tables, it also models the chances that a previously accessed page may have already been evicted when it is needed again.

What I think is happening to you:

  1. Hardware setup is different. Ram / CPU / Disk, it's not the same and it is influencing the estimations.
  2. Physical data files. How did you make a copy? I would recommend the only way to truly replicate this is to do a backup / restore with the data files.
  3. Did you try clearing out the cache and then forcing a recompile? I wonder what this would result in.

Otherwise I'd love to see the estimated and actual query plans to dive deeper into what looks like is going on.

IMPORTANT, THIS WILL HURT (You could be fired) IF YOU RUN THIS IN PRODUCTION WITHOUT UNDERSTANDING WHAT WILL HAPPEN AND WITHOUT PLANNING THIS. This is how I'd clear the cache to test again with recompile.


Different Ways to Flush or Clear SQL Server Cache by Bhavesh Patel - March 31, 2017 (archive)

  • DBCC FREESYSTEMCACHE
  • DBCC FREESESSIONCACHE
  • DBCC FREEPROCCACHE
|improve this answer|||||
  • The alternate database was a backup/restore of the main system (but a few weeks old at the time of these screenshots; i.e. 23M rows vs 25M rows). And recompile's were being forced (as you can see the OPTION(RECOMPILE). So it's an interesting problem: Add more RAM to a database server - performance goes down! – Ian Boyd Nov 7 '19 at 20:36
  • @IanBoyd Ah that makes sense. If the underlying file structure was the same and not modified, I don't think that would make a difference then. I'd be possibly concerned about statistics because they won't be the same. (It's still similar enough but I'd wonder if this caused any differences.) I'd still like to see what happens to estimated and actual query plans when you clear cache and force recompile. My bet is still on the hardware differences and the calculations around the pages in memory. – Shaulinator Nov 7 '19 at 20:41
  • @IanBoyd Why in Server A ordered is true, whereas in Server B is false? – dbilid Nov 7 '19 at 22:51
  • @dbilid I don't know. Presumably on Server B my forced use of the clustered index scan. Either way it's not important to the question, which is: "Why is 116 + 5 <> 119?" Because no matter how we got here, we have an operator with a cost estimate of 0.01 when it should be 119. – Ian Boyd Nov 8 '19 at 19:18
  • @IanBoyd I believe it is important, in order to see if in server b the clustered index is sorted in RowNumber, otherwise there is no meaning in expecting to reproduce the behaviour that total cost << I/O + CPU in server B – dbilid Nov 8 '19 at 22:32
4
+500

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

|improve this answer|||||
1

Can we presume the servers are genuinely identical?

  • cpu count
  • ram
  • sql service pack level
  • db compatibility level

I noticed a small change in query step costs returned for a SP execution plan after amending the db compatibility level on a sql2012 server. (idle db, got first plan xml, applied option change, recompiled sp, got second plan xml) The plan itself appears identical. More options are available within the optimiser, possibly calculates it slightly differently. If you have different patch/compatibility across the 2x servers it could result in the actual plan being more radically different (wrong..)

|improve this answer|||||
1

For me it seems absolutely normal that server A chooses the clustered index scan. This is the best decision given the knowledge that the optimizer has. The strange thing is that server B does not chooses the same. I think I have an answer for that, but first let me explain why the optimizer must choose the clustered index scan.

The basic reason has to do with the fact that it thinks that values in RowNumber and TransactionDate are independent. As it says here:

Independence: Data distributions on different columns are independent unless correlation information is available.

And the query is

SELECT MIN(RowNumber)   FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000'

The option are: 1) to start scanning the clustered index, which is sorted on RowNumber, and stop as soon it will encounter the first tuple with TransactionDate >= '20191002 04:00:00.000' which will be the actual answer to the query 2) to search the nonclustered index of TransactionDate for value '20191002 04:00:00.000' and then keep scanning the rest of the index from that value onward, keeping the minimum RowNumber that it will find

I am assuming here that value '20191002 04:00:00.000' is among the largest values in column TransactionDate. Actually, let's assume that it is larger than 95% of the values. Given the independence assumption, in option 1, it reasonable to assume that the answer will be found in a single disk fetch, as each tuple scanned has 5% probability to be the final answer. In option 2, searching the index for the specific date, already involves more disk page fetches, and then we also have to scan the 5% of the index. In reality though, as values in the two columns as directly correlated, what seems to the optimizer as the best option, ends up scanning 95% of the clustered index.

So, why Server B does not choose to scan the clustered index? Obviously, in Server B the clustered index is NOT sorted on RowNumber, as we can see from the plans posted in the original question: In server A result of scan is sorted, whereas in Server B it is not

So, why CPU_cost + I/O_cost >> cost. It seems that SQL server for clustered index scan reports the full table CPU and I/O cost, even if it is only a partial scan, and only reports the actual estimate based on how fast it will find the expected value as total cost. You can see the exact same behavior in the plan posted here

And as far as what can be done, if RowNumber and TransactionDate are always increasing, the query could be rewritten as follows:

SELECT RowNumber FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000' odrer by TransactionDate LIMIT 1

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.