Sql-server – Index Seek Operator Cost

database-internalsexecution-plansql server

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:

plan

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?

Best Answer

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

  2. The cardinality (total number of rows) in the index
    The TableCardinality property of the Index Seek operator gives this: 113,443

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

  4. 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

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

Result

Related Question