9

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?

10

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

  • Great work, it's nice to know where the estimated cost comes from, which variables affect it and in which proportion. Is this the mechanic for all SQL Server versions? Maybe a few changes in the constants? – EzLo Jul 3 '18 at 9:10
  • 2
    This aspect of the costing hasn't changed since 2005 as far as I am aware. – Paul White Jul 3 '18 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.