November 26, 2007 at 7:54 am
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.
November 26, 2007 at 8:23 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2007 at 8:32 am
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
November 26, 2007 at 9:32 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2007 at 11:15 am
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.
November 26, 2007 at 1:46 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply