November 7, 2016 at 9:52 am
Never had this issue before, i am creating a unique non-clustered index on a 15 GB table. When i execute sp_who2 and sys.sysprocesses shows that spid is in sleeping state ( don't see any changes in CPU, memory and IO), howerver query is still executing ( SSMS). I have also confirmed the index is created. Has anyone experienced this before? Looks like it is stuck in this weird stage.
November 7, 2016 at 12:28 pm
I think ssms client was just hung.:-D
November 7, 2016 at 1:15 pm
Did the BlkBy column indicate if the process was in a blocked state? If so, then the column would contain the SPID of the blocking process.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 7, 2016 at 1:20 pm
No blocking
November 7, 2016 at 1:42 pm
Check the wait_type column of dm_exec_requests to see if it's in an extended CXPACKET wait state. That's practically obligatory and normal for a large index operation running in parallel mode. There will be no external blocking and both overall CPU and I/O may be near idle during this wait state.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply