Sql-server – Why does this plan cost even though there are no rows

execution-plansql serversql-server-2008-r2

I execute a Batch-Operation. The whole execution needs about 60s. The following statement needs 75.6% of the whole Batch-Operation:

update WW_Pos set LieferantID=null where LieferantID in (select Ident from deleted)

But there are no rows in WW_Pos for this where-condition. As you can see in the execution-plan, the Update-Operation of the clustered index would take 99% of the costs of the plan:

enter image description here

and the Plan-Tree shows there are no Actual Rows for the Update-Operation:

enter image description here

LieferantID is not the Clustered index (but it is in another index WW_Pos_LieferantID). Nevertheless, it has to update the clustered index?

Why does this Update-Statement take about 40 seconds, even though there are no records to update?

I hope someone can help me understand this behavior.

Best Answer

The following statement needs 75.6% of the whole Batch-Operation

This is an estimate. The percentage costs are always estimates, even in a post-execution ('actual') execution plan. The 75.6% figure represents the estimated cost of this plan as a proportion of the estimated cost of the entire batch.

Given the disparity between estimated rows and the number actually encountered at runtime, this number means nothing. In fact, these numbers are often no better than a very loose hint, because the optimizer's cost model probably bears very little relation to the actual costs of execution in your particular hardware environment, even where the cardinality estimates happen to be exactly correct.

As you can see in the execution-plan, the Update-Operation of the clustered index would take 99% of the costs of the plan

Same explanation as above. According to the optimizer's cost model, if the query had actually produced the same number of rows as estimated, the Clustered Index Update would account for 99% of the estimated cost of running this statement.

LieferantID is not the Clustered index (but it is in another index WW_Pos_LieferantID). Nevertheless, it has to update the clustered index?

An update always has to modify the clustered index, since the clustered index is the table. Changes must be made in the base table as well as any nonclustered indexes (which are just copies of a subset of the underlying table data).

Why does this Update-Statement take about 40 seconds, even though there are no records to update?

Hard to say from the information provided. This may be a trigger plan (after or instead of update) assuming you do not have a table called [deleted], in which case it is possible you are timing the complete update statement, and not just the execution of this trigger code.

To address an extra question raised in comments to the question, the extra operators in the execution plan are to detect non-updating updates (a change to a nonclustered index that does not actually change the stored value).

Related Question