locked
update and insert got CXPacket Wait RRS feed

  • Question

  • a store proc is not finishing after 3 days. the cpu is constant at 30% and disk IO is next to 0. the sp_who2 shows multiple entries of update with CXpacket wait.

    how to rewrite a query to get it to complete?


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Tuesday, August 7, 2012 1:34 AM

Answers

All replies

  • Check the execution plan and confirm the indexes have been used properly.

    How many rows you are trying to fetch? Check the filer(if any) had been given properly.

    Please provide us the statement which is taking huge time and its execution plan.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, August 7, 2012 3:48 AM
  • Check the execution plan of complete store procedure to check where parallelism occurring that  leads to CXPACKET wait type.

    Check tables used in store procedure for indexes are appropriate or not.


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, August 7, 2012 4:56 AM
  • You cam 'remove' parallelism for that specific query by adding option(maxdop 1) hint .

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by light_wt Wednesday, August 8, 2012 5:48 PM
    Tuesday, August 7, 2012 5:26 AM
    Answerer
  • thank you everyone's reply.  I added option(maxdop 1) hint  and works great now, I think.  Still have one more day to wait. :)


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Wednesday, August 8, 2012 5:48 PM