SQL Server has a lot of different 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 two-hour 500-level session, we will dive deep into the bowels of the operator to learn how it performs. 

It is going to be wild ride, so keep your hands, arms, and legs inside the conference room at all times; and please remain seated until the presenter has come to a full stop. 

Topic covered in this session include:
* What is an in-memory hash table and how exactly is it built?
* The logical operations supported by Hash Match: what do they do and how do they work?
* Memory usage: what is a memory grant, which factors are used to compute/estimate it? What exactly happens when a Hash Match operator has to spill? (Dynamic destaging, dynamic role-reversal, bail-out, bit-vector filtering)
* How is memory divided when multiple operators in a single plan use a memory grant?
* Hash teams: What are they, when are they used, what is the benefit?
(no tags)
The video is not available to view online.