or
sql-server
Paul White
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
Top Answer
Paul White
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
Index Seek Operator Cost

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.