Sql-server – Meaning of terms in SQL Server execution plan

Before getting to the main answer, there are two pieces of software you need to update.

Required Software Updates

The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the time of writing is Service Pack 3, Cumulative Update 12 (build 5844).

The second piece of software is SQL Sentry Plan Explorer. The latest versions have significant new features and fixes, including the ability to directly upload a query plan for expert analysis (no need to paste XML anywhere!)

Query Plan Analysis

The cardinality estimate for the table variable is exactly right, thanks to a statement-level recompilation:

Unfortunately, table variables do not maintain distribution statistics, so all the optimizer knows is that there are six rows; it knows nothing of the values that might be in those six rows. This information is crucial given that the next operation is a join to another table. The cardinality estimate from that join is based on a wild guess by the optimizer:

From that point on, the plan chosen by the optimizer is based on incorrect information, so it's no wonder really that performance is so poor. In particular, the memory set aside for sorts and hash tables for hash joins will be far too small. At execution time, the overflowing sorts and hashing operations will be spilled out to physical tempdb disk.

SQL Server 2008 does not highlight this in execution plans; you can monitor the spills using Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.

Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.

Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE). You should also try materializing the result of the first join into another temporary table, and run the rest of the query against that. The number of rows is not all that large (3,285,620) so this should be reasonably quick. The optimizer will then have an exact cardinality estimate and distribution statistics for the result of the join. With luck, the rest of the plan will fall into place nicely.

Working from the properties shown in the plan, the materializing query would be:

SELECT
    A.A_Var7_FK_LK,
    A.A_Var4_FK_LK,
    A.A_Var6_FK_LK, 
    A.A_Var5_FK_LK,
    A.A_Var1,
    A.A_Var2,
    A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
    ON A.Job = B.B_Var1_PK
WHERE
    A_Var8_FK_LK = @Param1;

You could also INSERT into a predefined temporary table (the correct data types are not shown in the plan, so I cannot do that part). The new temporary table may or may not benefit from clustered and nonclustered indexes.

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).