query plan shows cost of 54% for an insert when no rows actually involved

Question

In one of my queries there's an insert of data into a temp table. Looking at the query plan, it shows the the actual insert into temp table took 54% (just inserting data into temp table). However, no rows are being inserted into the temp table.

Why does the plan show a non zero value when no rows are being inserted?

Answer 1

Even in the actual query plan the subtree costs shown are based on estimates as well as various heuristics and magic numbers used by the cost based optimiser. They can be woefully wrong and should be taken with a big pinch of salt.

Example to reproduce

create table #t
(
i int
)

insert into #t
select number
from master.dbo.spt_values
where number = 99999999

The actual insert was zero rows but the estimate was 1 row which is where the subtree cost comes from.

Edit: I just tried the following

insert into #t
select top 0 number
from master.dbo.spt_values
where number = 99999999

Even when it gets the estimated number of rows right it still assigns a small non zero cost to the insert. I guess the heuristic it uses always assigns some small element of fixed cost.