BPSORT + CXCONSUMER Waits - DEV vs QA server

• jsqldba

  SSChampion

  Points: 11257

  More actions

  May 24, 2019 at 6:53 am

  #3646780

  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: 80342

  More actions

  May 25, 2019 at 7:10 am

  #3647224

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

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