Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

User processes on Suspended status Expand / Collapse
Author
Message
Posted Monday, November 26, 2007 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2007 6:42 AM
Points: 8, 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.
Post #425773
Posted Monday, November 26, 2007 8:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 15,735, Visits: 28,139
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #425811
Posted Monday, November 26, 2007 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2007 6:42 AM
Points: 8, 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
Post #425819
Posted Monday, November 26, 2007 9:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 15,735, Visits: 28,139
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #425858
Posted Monday, November 26, 2007 11:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #425916
Posted Monday, November 26, 2007 1:46 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 6,266, Visits: 2,029
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
Post #425997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse