SQL Server - Execution Plan

Help! My question is slow. The next time you execute the query, there will be an additional tab in the results section. Where to start? Usually a good place is the execution plan for the query. Easiest way to see it is in Management Studio. Bring up the current query in a window and press ctrl+M.

The next time you execute the query, there will be an additional tab in the results section.

So where do you start? Of course, you can delve as far as you like, but I'm going to keep it very simplistic.

To understand how the question is executed, start reading it from left to right, top to bottom. Each icon in the execution plan is an operation that corresponds to one of almost 100 different events or decisions that take place when the question is being processed. The flow of data is represented by the arrows between the icons and their thickness corresponds to the amount of data and goes in turn from right to left. Therefore, one should aim to move predicates as far to the right as possible to keep the amount of data to be handled down.

To optimize the execution plan, there are a few icons to keep an extra eye on:
Lookup - Predicate that is satisfied by an unclustered index but lacks values on one or more columns and therefore must go back and retrieve those values from the hash table or the clustered index.
Spool - Acts as a form of Cache in the execution plan. It occurs when the correct index is missing or has poor uniqueness. Created as hidden tables in tempdb.
Sort - ORDER BY, merge joins, stream aggregates, windowing. Scales poorly, gets much worse as data size increases. Check indexes and if sorting is really needed or can be moved to application side for example.
Hash - Aggregation and joins. Scales linearly but can/will spill to tempdb. Check index.
Serial Nested Loops - Joins. Often the culprit when a question goes well one time but not the next. Works best with some data in the outer loop. Check statistics how the order is done in the join.

Bonus

Scans - Can be bad, depends on how many lines are handled/returned. Keep an eye out for implicit data type conversions in predicates. May cause index scans to perform poorly in some cases.
Another bonus that Management Studio gives you if you examine an execution plan is that it warns you of missed indexes and tells you what it's missing. You can find this directly after each part of the question in green text.