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.

  • 2
    You should include both execution plans in your question – sepupic Feb 1 '19 at 18:58
  • 5
    The likely answer is that it's using the new cardinality estimator, but please share the plans. – Erik Darling Feb 1 '19 at 19:02
  • 1
    Wow, just wow Erik. That was it. I'll have to research this more, I would have never found that particular rabbit hole to go down. Thank you! – Vale Trujillo Feb 1 '19 at 19:15
  • 2
    Here's a recent, very relevant article for you: sqlperformance.com/2019/01/sql-performance/… – Jacob H Feb 1 '19 at 19:22
  • No problem at all! Always gotta read that fine print. – Erik Darling Feb 1 '19 at 19:46