I’m going to talk about my favorite example, because it can cause a lot of confusion, and can hide a lot of the work it’s doing behind what appears to be a friendly little operator.
Something to keep in mind is that I’m looking at the actual plans. If you’re looking at estimated/cached plans, the information you get back may be inaccurate, or may only be accurate for the cached version of the plan. A query plan reused by with parameters that require a different amount of work may have very different numbers.
I like nested loop joins a lot, but there’s a big difference between a loop running a few dozen times and a loop running a couple hundred thousand times, even if the operator doesn’t show you that immediately.