August 2, 2011 at 11:17 pm
Hi
session_id : 53
status : suspended
blocking_session_id : 0
wait_type : CXPACKET
wait_time : 60953
wait_resource :
transaction_id : 5800591
I checked if any session id blocked or not, I got one session_id (53) was suspended on 8:30 AM, but actually this session was not blocked for comes knew blocking_session_id : 0.
So still this session is appear but not clear automatically,
dbcc inputbuffer(53)
SELECT wrkr_code,BRIBS_ENROLL_FLAG FROM [WRKR_MST] WHERE ([wrkr_CODE] = '00066058') and loc_code = 'NC'
Please tell me, how to clear this session id? or by using kill command. or any other way.
Thanks
ananda
August 2, 2011 at 11:35 pm
CXPACKET waits are mostly due to parallelism. What is the max degree of parallelism for the server? Is there any hint (MAXDOP) for the queries which are causing the waits?
Check the cpu and disk I/O for this session id you have mentioned. If it's CPU or I/O is not being utilized then try killin git with Kill SPID. If still it's not killed then check the Unit Of Work (UOW) and kill all UOWs except all 0's.
August 2, 2011 at 11:47 pm
max degree of parallelism
min - 0
max - 64
config value - 0
Run_value - 0.
as per above sp_configure only default setting. not extra configure on this server.
and there is not MAXDOP hit,
Can you tell me, how to clear this suspended process?
Thanks
August 2, 2011 at 11:50 pm
Hi ,
As you reply...
If it's CPU or I/O is not being utilized then try killin git with Kill SPID. If still it's not killed then check the Unit Of Work (UOW) and kill all UOWs except all 0's.
can you give me script for check UOW.
August 3, 2011 at 12:09 am
select distinct req_transactionUOW from sys.syslockinfo where req_spid = 53
kill 'UOW' <-- The distinct UOW's except all 0's.
August 3, 2011 at 12:18 am
sujitkmishra (8/3/2011)
select distinct req_transactionUOW from sys.syslockinfo where req_spid = 53kill 'UOW' <-- The distinct UOW's except all 0's.
Thanks for reply...
select distinct req_transactionUOW from sys.syslockinfo where req_spid = 53
result is - 00000000-0000-0000-0000-000000000000
Kill 53
Please tell me, for future avoid this issue,
Can I configure max degree of parallelism set to 1.
August 3, 2011 at 12:27 am
It's the base ID and I won't risk try killing it. Not sure if it can be killed except bouncing SQL engine.
If the process is there then there should not be any issue as it's orphaned.
Now coming to setting the maxdop, well, if your application is not well designed for parallel processing then better go for value 1. Please bear in mind that it's a major change and application should be tested well in a test box with multiple processor. Keep monitoring the wait types in the test box during testing.
August 3, 2011 at 12:59 am
select distinct req_transactionUOW from sys.syslockinfo where req_spid = 53
kill 'UOW' <-- The distinct UOW's except all 0's.
result is - 00000000-0000-0000-0000-000000000000 - please clarify me little confused, It means UOW's are zero, and there is no active this SPID 53, can able to KILL this SPID and No issue SQL engine side.
Thanks.
August 3, 2011 at 1:32 am
sujitkmishra (8/2/2011)
If still it's not killed then check the Unit Of Work (UOW) and kill all UOWs except all 0's.
The Unit of work is purely for distributed queries running through MSDTC. The Unit of work will always be 0 for all local queries, regardless of how they parallel.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2011 at 1:38 am
ananda.murugesan (8/2/2011)
Hisession_id : 53
status : suspended
blocking_session_id : 0
wait_type : CXPACKET
wait_time : 60953
wait_resource :
transaction_id : 5800591
I checked if any session id blocked or not, I got one session_id (53) was suspended on 8:30 AM, but actually this session was not blocked for comes knew blocking_session_id : 0.
CXPacket is a parallel skew, it means that some threads of the parallel query are waiting for other threads. The CXPacket wait itself is not of interest or really concern.
If you query sys.dm_exec_requests for that spid, there will be multiple rows (one for each thread). At least one of those will have a wait type that is not CXPacket. Please find out what it is and what it's wait_resource is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2011 at 1:53 am
Hi, Thanks for reply...
SELECT session_id ,status ,blocking_session_id ,wait_type ,wait_time ,wait_resource ,transaction_id
FROM sys.dm_exec_requests WHERE session_id = 53
In this case wait_resource is - Blank. I could not find out which wait resource occupied.
August 3, 2011 at 2:06 am
One of those will have a wait_type other than CXPacket. What is that wait_type an what, if any, is the wait_resource?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 3:48 pm
I have the same problem. The wait_source of the active session is blank (NULL). The last wait resource was SOS_SCHEDULER_YIELD.
I'm trying to do multiple inserts into the same table.
session_idblocking_session_idStatusCommandwait_typeopen_transaction_countwait_resource
590runnableINSERTNULL1
600suspendedINSERTCXPACKET2
610suspendedINSERTCXPACKET2
620suspendedINSERTCXPACKET2
630suspendedINSERTCXPACKET2
640suspendedINSERTCXPACKET2
650suspendedINSERTCXPACKET2
660suspendedINSERTCXPACKET2
670suspendedINSERTCXPACKET2
680suspendedINSERTCXPACKET2
690suspendedINSERTCXPACKET2
700suspendedINSERTCXPACKET2
710suspendedINSERTCXPACKET2
August 10, 2011 at 3:54 pm
Read this and then start a new thread if necessary
http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply