I have migrated an old, ugly query from a 2008 SQL Server to a new 2016 instance. The query runs fine on the old database even when the query plan has to be recreated. On the new database the query just runs forever and it does not seem like it is even making it through the execution plan. I can tell from my research that CXPACKET, CXCONSUMER, AND IO_COMPLETION waits are very high and it seems like this query is spilling out into tempdb heavily.
At this point I'm pretty much resigned to rewriting the thing but I am really curious as to why it is just fine on the 2008 box. The old database has less memory and slower CPUs. Both are VMs with the same MDOP and CTP values.
Any advice or insight is appreciated.