SQL Relationship between Subtree Cost and Performance Time

The subtree cost represents the estimated cost of a plan. It can be useful when investigating why the query optimizer chose one plan over another. For example, you might see a plan with a hash join and think that a loop join would have been a more efficient choice. Adding a query hint to force a loop join and comparing subtree costs can be helpful in determining why SQL Server chose a hash join.

The estimated cost of the plan will often not match the "performance time" of a query for many reasons, including hardware differences, blocking by other processes, overall server workload, model limitations, and assumptions based on imperfect information. In addition, a subtree cost of 0.1 versus 0.2 is really not a meaningful difference at all. If you have a query that has a low relative cost to the rest of your workload but that query runs for a long time that's a hint that the query optimizer is making an incorrect assumption or deduction. The root cause of those types of issues often comes down to cardinality estimates. On the other hand, sometimes a relatively expensive query will run for a long time. Looking at the parts of the plan estimated to take a long time can provide useful clues as to why the query is running for a long time. However, some query tuners will tell you to not look at estimated costs at all.

Below are a few example queries just to show that it's possible to have an extreme difference between the estimated cost and the run time. I'm testing on SQL Server 2017, but it's possible to come up with similar demos in all versions. First I put 100k sequential integers into a heap:

CREATE TABLE dbo.OptimizerUnits (ID BIGINT NOT NULL);

INSERT INTO dbo.OptimizerUnits WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

Consider the following query:

SELECT ID
FROM dbo.OptimizerUnits 
WHERE 
(ID % 10) % 101  = 10

A human can look at that query and deduce that it won't return any rows, but the optimizer currently doesn't have that kind of logic built-in. It instead guesses that about 990 rows will be returned. That gives the following query a total estimated cost of 79590.1 units:

WITH OptimizerUnitsCTE (ID) AS 
(
    SELECT ID
    FROM dbo.OptimizerUnits 
    WHERE 
    (ID % 10) % 101  = 10
)
SELECT TOP (100) t1.ID, t2.ID, t3.ID
FROM OptimizerUnitsCTE t1
CROSS JOIN OptimizerUnitsCTE t2
CROSS JOIN OptimizerUnitsCTE t3
ORDER BY t1.ID + t2.ID + t3.ID DESC;

However, the query runs in under 50 ms on my machine.

Now let's go in the other direction. Consider the following query:

SELECT ID
FROM dbo.OptimizerUnits 
WHERE 
(ID % 10) % 101  = 1
AND (ID % 10) % 102  = 1
AND (ID % 10) % 103  = 1
AND (ID % 10) % 104  = 1

Once again, a human could deduce that the above query will return exactly 10000 rows. The query optimizer does not know that and it guesses that the query will return just 16.7439 rows. This results in an estimated cost of 1.45306 optimizer units for the following query:

WITH OptimizerUnitsCTE (ID) AS 
(
    SELECT ID
    FROM dbo.OptimizerUnits 
    WHERE 
    (ID % 10) % 101  = 1
    AND (ID % 10) % 102  = 1
    AND (ID % 10) % 103  = 1
    AND (ID % 10) % 104  = 1
)
SELECT TOP (100) t1.ID, t2.ID, t3.ID
FROM OptimizerUnitsCTE t1
CROSS JOIN OptimizerUnitsCTE t2
CROSS JOIN OptimizerUnitsCTE t3
ORDER BY t1.ID + t2.ID + t3.ID DESC;

I ran the query for a while on my machine and estimate that it would take around 4.5 days to complete.

In summary, poor cardinality estimates made a query with a cost of 79590.1 units take under a second and a query with a cost of 1.45306 units take about 4.5 days.

SQL Server I/O and CPU Cost is an estimate of seconds from the year 2000.

SQL Server estimates that each I/O will take 3.125 ms (i.e. 1⁄320 s, because of the assumption that the disk can perform 320 I/O operations per second. 1⁄320 = 0.003125). Each I/O is fetching an 8 KB page from the disk.

This is one of the "magic numbers" inside the SQL Server optimizer.

The others are:

| Item                       | Cost (seconds) |
|----------------------------|----------------|
| I/O cost (per page)        | 0.0031250 s    |
| CPU cost (first row)       | 0.0001581 s    |
| CPU cost (additional rows) | 0.0000110 s    |

So if you had a query with:

• I/O Cost: 2.82387 s

This means it estimated: 2.82387 s ⁄ 0.003125 s⁄IO = 903.6384 I/O pages

Note: Just because they were seconds doesn't mean they are seconds. The cost isn't implying that I/O will take 2.82 s. Today it's just a unitless magic number; but that magic number does have origins.