I've got a process which does a lot of ETL type queries which runs fine on our DEV server but not on QA.
SQL 2016 developer edition (prod is on Standard, haven't tested there yet)
DEV = 4CPU, 16GB sql instance, maxdop = 2
QA = 8CPU, 32GB sql instance, maxdop = 4
On DEV, the script runs fine. On QA, a step gets hung (forever) with massive BPSORT and CXCONSUMER. I haven't been able to find a lot of good info on these wait types in this situation, and it's the first time I've ever seen them. i managed to reduce them by optimizing their query a bit (i'm the dba, testing and tuning for the developers). the offending code was and ORDER BY in an INSERT BEGIN/WHILE loop, the order by was unnecessary since the data is loaded by cluster key order. Removing this eliminated completely the waits. Before that, I tested different maxdops and nothing changed.
So my question, what could be the reason for this manifesting itself in QA and not in DEV?