Hash Match, the Operator - Part 2

SQL Server has a lot of difference execution plan operators. By far the most interesting, and the most versatile, has to be the Hash Match operator.
Hash Match is the only operator that can have either one or two inputs. It is the only operator that can either block, stream or block partially. And it is one of just a few operators that contribute to the total memory grant of an execution plan.

If you ever looked at execution plans, you will have seen this operator. And you probably have a rough idea of what it does. But do you know EXACTLY what happens when this operator is used? In this extended 500-level session, we dived deep into the bowels of the operator to learn how it performs.

In the second part, we explored memory usage of the Hash Match operator. How is the memory grant determined? When there's insufficient memory and the operator "spills to tempdb", what happens exactly? We looked at all the phases of dynamic destaging, and ran some demos where you can see this in action.

We also looked at how execution plans with multiple Hash Match operators allow them to share, or not share, used memory. The session ended with a brief look at a thing called "Hash Teams".