Post reply

High CXCONSUMER waits

  • November 27, 2019 at 2:19 pm

    #3701542

    Hello

     

    I seem to have a moving target with SPs called via a number of ETL jobs

    The reason I say it's a moving target is that this happens, maybe every couple of weeks (random) and it seems to be a different query each time.

     

    Yesterday, a job that ordinarily takes less than an hour took in excess of 6

    I've found the culprit - a single query that been stuck on 100% CXCONSUMER waits

    Eventually, it cleared

     

    Problem is, I'm struggling to find the source of the issue

    Seems to be no blocking and nothing much else going on

    CPU utilisation is around 60%

    The only thing that bombs is page life expectancy although that's still aver 500

     

    Any ideas where to look on this one?

     

    Thanks.

    - Damian

  • November 27, 2019 at 3:15 pm

    #3701574

    Showing up as CXCONSUMER in solar winds

    Yet, I have 12 suspended update CXPACET instances in sys.dm_exec_requests / sys.processes / sys.dm_exec_sql_text

    - Damian

  • November 27, 2019 at 3:25 pm

    #3701579

    CXPACKET and CXCONSUMER waits mean that your queries are going parallel... but not necessarily that the parallelism is a problem.

    How do your ETL jobs work - with stored procedures or with ad hoc SQL?  Query your plan cache to see which queries are using the most resources.  If the queries are parameterised, consider whether parameter sniffing could be the problem.  If they're not, consider whether you've got a lot of unnecessary recompiles.  Give the query optimizer the best chance of getting the best execution plan by regularly updating your statistics.

    John

  • November 27, 2019 at 4:46 pm

    #3701609

    Thanks John.

    ETL works with SP's

    I'll take a look at some of your suggestions

     

    - Damian

Viewing 4 posts - 1 through 3 (of 3 total)

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