Did you check what the wait types are for the slow queries? Are you using Linked Servers? I think there's a known issue with connecting to databases lower than 2012,...
I am sorting on the source table's keys so it can't be non-unique records that is causing this, but thanks for the Union All tip. The CPU usage went from...