# 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

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

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

• 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
Commented Jul 3, 2018 at 9:10
• This aspect of the costing hasn't changed since 2005 as far as I am aware. Commented Jul 3, 2018 at 9:11