Why does outer apply cause a broadcast move?

Question

I've got an outer apply with a condition on the distribution keys.

select e.a
      ,e.b
      ,p1.c
  from e 
  outer apply
  (
    select top 1 
    p.DateStamp
    from p
    where e.distributionKey = p.distributionKey
    and p.client = e.client
    and p.DateStamp > e.DateStamp
    order by p.DateStamp
  )
  as p1;

Using Explain I can see that this causes a broadcast move

<?xml version="1.0" encoding="utf-8"?>
<dsql_query number_nodes="1" number_distributions="60" number_distributions_per_node="60">
    <sql></sql>
    <dsql_operations total_cost="9047512.32" total_number_operations="5">
        <dsql_operation operation_type="RND_ID">
            <identifier>TEMP_ID_1231</identifier>
        </dsql_operation>
        <dsql_operation operation_type="ON">
            <location permanent="false" distribution="AllComputeNodes" />
            <sql_operations>
                <sql_operation type="statement"></sql_operation>
            </sql_operations>
        </dsql_operation>
        <dsql_operation operation_type="BROADCAST_MOVE">
            <operation_cost cost="9047512.32" accumulative_cost="9047512.32" average_rowsize="308" output_rows="122396000" GroupNumber="12" />
            <source_statement></source_statement>
            <destination_table>[TEMP_ID_1231]</destination_table>
        </dsql_operation>
        <dsql_operation operation_type="RETURN">
            <location distribution="AllDistributions" />
            <select></select>
        </dsql_operation>
        <dsql_operation operation_type="ON">
            <location permanent="false" distribution="AllComputeNodes" />
            <sql_operations>
                <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_1231]</sql_operation>
            </sql_operations>
        </dsql_operation>
    </dsql_operations>
</dsql_query>

However, the first line of my where clause should mean the query is distribution aligned

where e.distributionKey = p.distributionKey

Why is this not the case?

Why does outer apply cause a broadcast move?

0 Answers0