July 26, 2016 at 6:56 am
Hi,
i'm noticing, what I believe is, an interesting issue on one of my SQL Server. I am running stored proc which takes some data from one DB and populates another with some very basic where clause.
The total result count I am expecting is X. All seems to run fine until I hit "X-70"(the current count in the destination table) at which point the SPID shows 5 rows in the from my waiting dmv script (sys.dm_exec_requests r join sys.dm_os_tasks) all of which have a wait type of CXPACKET. There is one of the rows reporting a task_state of "Running" which all others are suspended. The running spid also has a exec_Contect_id of 3.
What is confusing me is which this would run fine until its get to 70(!!!) rows from completing and it just hangs there, not inserting an more rows and with one of the threads still running with the others waiting? Is there a why I can see what this spid is still doing? The Wait_time on all threads is the same, which is expected but they are quite high like nothing is progressing.
- MAXDOP is currently set to 4 on a 4 core machine with CTOP at 30.
- There is space in the DB
- Instant file initialization is enable
Hopefully this is enough information.
Any suggestions would be greatly appreciated as I'm at a loss at the moment.
Many thanks
July 26, 2016 at 8:20 am
I've also noticed that in sysprocesses we also have 5 rows with 4 showing CXPacket as the last wait time with huge amounts of waittime while the other shows SOS_SCHEDULER_YIELD and a waittime of 0
July 26, 2016 at 8:36 am
Quick suggestion, post the actual execution plan and the full specs of the server
😎
July 26, 2016 at 9:04 am
A CXPACKET (packet exchange) wait state means that one or more operations in your execution plan is running parallel and execution has paused to wait for one of the threads to catch up with it's portion of the workload. Ideally the workload will be balanced between each thread, so multitasking is maximized. SQL Server depends on table statistics to divide the workload evenly, so if statistics are stale, you could end up with the bulk of the load allocated to one thread. So, one thing to try when troubleshooting queries that get stuck for extended periods of time in a CXPACKET wait state is to insure that statistics on the tables are updated.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 27, 2016 at 2:32 am
Hello folks,
Thank you for your responses. I believe I have found the issue and it was simply that the SP has a final insert which was using a subquery to join a subset of a table to the query. I've now taken this out and used a temp table and the progress runs without any delays.
Many thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply