Clustered Index Seek: performed where there is a WHERE clause and the search parameter is included in the clustered index.

Clustered Index Scan: This task is used when there is no WHERE clause, so the Query Optimize knows you want all rows and it will simply scan all the data pages (which it would have to do anyways) to return all the data in the SELECT statement.

Key Lookup: usually means there is a column referenced in the query that is not a part of a clustered or non-clustered index, and thus, will require this task, as well as a nested join to join the 2 resultsets.

Hash Join: works well with tables that have no indexes and need to be joined with a large result-set.

Compute Scalar: A simple, inexpensive task that outputs a single value, like form a calculation or conversion like UCASE(”).

Merge Join: This task works only with sorted data, using 2 resultsets of sorted data to simply merge them together. However, if the data is not sorted, then the Query Optimizer will need to add a Sort task, which will make the Merge Join task less efficient overall.

Sort: The general rule of thumb is that if the Sort task takes more than 25% of the overall plan cost, then you need to review the query carefully and optimize it because it is likely missing a WHERE statement and is pulling and sorting way too much data. This task appears when there is an ORDER BY statement in the query

Filter: Including a HAVING clause will add this taks to the execution plan. This task usually is not executed until all aggregation for the plan is complete.

NOTES

You should always pay attention to the difference between actual and estimated row counts in execution plans. A large discrepancy between these two values is often a sign of cardinality estimation errors resulting from inaccurate statistics.

Ref: Pro SQL Server Internals 2016, Apress,  pg 473

Views – 1990