Index Seek Operator Cost

For the AdventureWorks 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:

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?

The full cost derivation logic is complex, but for the relatively simple case in the question:

Inputs

-
The number of times the operator is executed

This is the Estimated Number of Executions: 504

-
The cardinality (total number of rows) in the index

The TableCardinality property of the Index Seek operator gives this: 113,443

-
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');

-
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;

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

StackExchange Database Administrators Q#211213, answer score: 12