For the [*AdventureWorks*][1] sample database query below:
SELECT
P.ProductID,
CA.TransactionID
FROM Production.Product AS P
CROSS APPLY
(
SELECT TOP (1)
TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE
TH.ProductID = P.ProductID
ORDER BY
TH.TransactionID DESC
) AS CA;
The execution plan shows an *Estimated Operator Cost* of **0.0850383** (93%) for the *Index Seek*:
[![plan][2]][2]
The cost is independent of the cardinality estimation model in use.
It is not a simple addition of the *Estimated CPU Cost* and *Estimated I/O Cost*. Neither is it the cost for one execution of the *Index Seek* multiplied by the *Estimated Number of Executions*.
How is this cost number arrived at?
[1]: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
[2]: https://i.stack.imgur.com/6FDuw.png
The full cost derivation logic is complex, but for the relatively simple case in the question:
### Inputs
1. The number of times the operator is executed
This is the *Estimated Number of Executions*: **504**
0. The cardinality (total number of rows) in the index
The *TableCardinality* property of the *Index Seek* operator gives this: **113,443**
0. The number of data pages in the index: **201**
This number can be obtained multiple ways, for example from `sys.allocation_units`:
SELECT
AU.data_pages
FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
ON P.hobt_id = AU.container_id
WHERE
AU.[type_desc] = N'IN_ROW_DATA'
AND P.[object_id] = OBJECT_ID(N'Production.TransactionHistory', N'U')
AND P.index_id =
INDEXPROPERTY(P.[object_id], N'IX_TransactionHistory_ProductID', 'IndexID');
0. The density (1 / *distinct values*) of the index: **0.002267574**
This is available in the density vector of the index statistics:
DBCC SHOW_STATISTICS
(
N'Production.TransactionHistory',
N'IX_TransactionHistory_ProductID'
)
WITH DENSITY_VECTOR;
[![density][1]][1]
### Computation
-- Input numbers
DECLARE
@Executions float = 504,
@Density float = 0.002267574,
@IndexDataPages float = 201,
@Cardinality float = 113443;
-- SQL Server cost model constants
DECLARE
@SeqIO float = 0.000740740740741,
@RandomIO float = 0.003125,
@CPUbase float = 0.000157,
@CPUrow float = 0.0000011;
-- Computation
DECLARE
@IndexPages float = CEILING(@IndexDataPages * @Density),
@Rows float = @Cardinality * @Density,
@Rebinds float = @Executions - 1e0;
DECLARE
@CPU float = @CPUbase + (@Rows * @CPUrow),
@IO float = @RandomIO + (@SeqIO * (@IndexPages - 1e0)),
-- sample with replacement
@PSWR float = @IndexDataPages * (1e0 - POWER(1e0 - (1e0 / @IndexDataPages), @Rebinds));
-- Cost components (no rewinds)
DECLARE
@InitialCost float = @RandomIO + @CPUbase + @CPUrow,
@RebindCPU float = @Rebinds * (1e0 * @CPUbase + @CPUrow),
@RebindIO float = (1e0 / @Rows) * ((@PSWR - 1e0) * @IO);
-- Result
SELECT
OpCost = @InitialCost + @RebindCPU + @RebindIO;
[*db<>fiddle*][3]
[![Result][2]][2]
[1]: https://i.stack.imgur.com/X9wed.png
[2]: https://i.stack.imgur.com/Zu21K.png
[3]: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6fe4fadf9f37f451e5c49b8c0a3e6e25
