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

select e.a
  from e 
  outer apply
    select top 1 
    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">
    <dsql_operations total_cost="9047512.32" total_number_operations="5">
        <dsql_operation operation_type="RND_ID">
        <dsql_operation operation_type="ON">
            <location permanent="false" distribution="AllComputeNodes" />
                <sql_operation type="statement"></sql_operation>
        <dsql_operation operation_type="BROADCAST_MOVE">
            <operation_cost cost="9047512.32" accumulative_cost="9047512.32" average_rowsize="308" output_rows="122396000" GroupNumber="12" />
        <dsql_operation operation_type="RETURN">
            <location distribution="AllDistributions" />
        <dsql_operation operation_type="ON">
            <location permanent="false" distribution="AllComputeNodes" />
                <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_1231]</sql_operation>

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?

  • One for @JRJ... – wBob Jan 22 '18 at 22:42
  • 1
    Have you tried simplifying the query by removing the predicates on client and date stamp. that will help you determine if the outer apply is the cause or something else. you may want to try looking into the sys.dm_pdw_sql_requests dmv for more insights into the sql that is being pushed into the nodes. my guess tells me the outer apply is opaque to the DMS – JasonHorner Feb 14 '18 at 13:27
  • @JasonHorner Thanks, I needed a little nudge. Interestingly, it doesn't move data when the greater than is removed, so that appears to be the cause – Neil P Feb 14 '18 at 13:56
  • makes sense the presence of the theta join and not the outer apply is the cause. recall that data movement will occur when incompatible joins are used there is a section on this in the APS chm file: microsoft.com/en-us/download/details.aspx?id=51610 – JasonHorner Feb 14 '18 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.