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?
The cost of 54 doesn't mean that rows need be involved. Perhaps there was an index scan or other seek operation or perhaps some non-optimal lookup in the WHERE clause of that INSERT into temp table?
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.
take a look at this
insert into #temp
select * from sometable
where left(Somecol,3) = 'BLA'
that is not sargable so it will cause a scan, but if no rows are found the insert doesn't happen...the scan still happens
but if you did this then the cost should drop dramatically because now the index can be used
insert into #temp
select * from sometable
where Somecol like 'BLA%'
BTW I would use STATISTICS TIME and STATISTICS IO instead to measure performance, those two are much better indicators..when you see 3 reads vs 10000 reads you know what is happening..what does 45% tell you exactly when the whole process could run 3 minutes or 3 seconds