BPSORT + CXCONSUMER Waits - DEV vs QA server

  • jsqldba

    SSChampion

    Points: 11256

    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?

    thanks!

     

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply