SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User processes on Suspended status


User processes on Suspended status

Author
Message
Songezo Rexe
Songezo Rexe
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 40
I'm trying to bulk insert some data which has slowed down to a crawl after going at a rate that I'd expect for this type of setup in the beginning.
Activity monitor shows the process as suspended ... now and then it seems to wake up and insert a few thousand rows, then it's in suspended again. I have this feeling it may be I/O contention .

All user processes are on suspect status. and the server is very slow.
Your help will be highly appreciated.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97669 Visits: 33014
You're getting wait states, or contention in your system. For a quick & dirty look at what's happening, query the DMV sys.dm_os_waiting_tasks while the suspended status is going on. That'll tell you what's blocking it.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Songezo Rexe
Songezo Rexe
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 40
This is what i get from the sys.dm_os_waiting_tasks. What do i do from here. pls help.


wait_duration_ms wait_type
-------------------- ------------------------------------------------------------
2688 LOGMGR_QUEUE
2047 BROKER_RECEIVE_WAITFOR
1125 REQUEST_FOR_DEADLOCK_SEARCH
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
63 PAGEIOLATCH_SH
0 OLEDB
366170563 KSOURCE_WAKEUP
110 PAGEIOLATCH_SH
354438 CHECKPOINT_QUEUE
0 PAGEIOLATCH_SH
366174656 ONDEMAND_TASK_QUEUE
16 PAGEIOLATCH_SH
1797 SQLTRACE_BUFFER_FLUSH
366173219 BROKER_TRANSMITTER
63 PAGEIOLATCH_SH
366108391 BROKER_EVENTHANDLER
3406 BROKER_TASK_STOP
16 PAGEIOLATCH_SH
375 LAZYWRITER_SLEEP
366173219 BROKER_TRANSMITTER
203 PAGEIOLATCH_SH
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97669 Visits: 33014
I think you mis-pasted the results there. You should see a session_id. I know you can identify the blocked session id. From that, you look at the wait_type column to see what it is waiting on. You'll probably also see the blocking_session_id. Use that to see what the other session is doing. With the wait information (like the deadlock at the top of what you have pasted, that's not good) and the blocking session's ID, you can figure out what's preventing you from moving forward.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13079 Visits: 23078
SQL "Suspends" a process when it is waiting for a non-SQL resource to complete an action. A very common example would be that a process has requested a large amount of data and the disk drives are busy processing something else. SQL will suspend a process like this until the drives respond with the requested data.

If you are seeing a lot of suspended processes, you need to use perfmon or some other tool to determine what SQL is waiting for. Check your CPU, memory, and drive usage - keep in mind that drive usage goes up if memory needs to get moved in and out of the swap file.
noeld
noeld
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22520 Visits: 2048
Songezo Rexe (11/26/2007)
This is what i get from the sys.dm_os_waiting_tasks. What do i do from here. pls help.


wait_duration_ms wait_type
-------------------- ------------------------------------------------------------
2688 LOGMGR_QUEUE
2047 BROKER_RECEIVE_WAITFOR
1125 REQUEST_FOR_DEADLOCK_SEARCH
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
54875 CXPACKET
63 PAGEIOLATCH_SH
0 OLEDB
366170563 KSOURCE_WAKEUP
110 PAGEIOLATCH_SH
354438 CHECKPOINT_QUEUE
0 PAGEIOLATCH_SH
366174656 ONDEMAND_TASK_QUEUE
16 PAGEIOLATCH_SH
1797 SQLTRACE_BUFFER_FLUSH
366173219 BROKER_TRANSMITTER
63 PAGEIOLATCH_SH
366108391 BROKER_EVENTHANDLER
3406 BROKER_TASK_STOP
16 PAGEIOLATCH_SH
375 LAZYWRITER_SLEEP
366173219 BROKER_TRANSMITTER
203 PAGEIOLATCH_SH


you should order those results by "duration"


* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search