The query was generated by a ETL tool. When I run it, sp_who2, shows 50+, copies of the same query (threads). Most of the queries are suspended and 1 or 2 are runnable but those queries can run for hours, all the while still showing 48 suspended threads of the same task. A wierd part of this is that while the query is running, Windows Task Manager shows CPU around 0%, 40GB free RAM, and disk activity near 0 (no hard faults). I can't figure out what is going on.
In PLan explorer my eye was drawn to the "Hash Match" (76%). But then it fell on the Concatenation below the hash match, which reportedly shows something like 6.4 billion rows! This is an estimated execution plan as I never got the query to complete.