Any idea why sp_MSdistribution_cleanup is so CPU intensive?
The query in question contains horribly nested correlated subqueries that the optimizer has no hope of unrolling from nested loops form. If you were to capture an execution plan with runtime statistics, you would find a very poor distribution of rows across threads in the parallel plan (quite likely all rows on one thread), and a simply enormous number of executions on the inner side of one or more nested loops joins.
The sheer number of inner-side executions is responsible for the high CPU usage, logical read count, and duration. If the code were better, the worst of the correlated loops joins would be replaced by a much more efficient (in this case) hash or merge joins and the thing would fly along. Sadly, we do not live in that particular universe.