How can I explain why my request takes so long?

Question

I have a request that takes a lot longer than usual and I can’t tell if it’s stuck.

The query looks something like this:

INSERT XXXXXX WITH (TABLOCK)
SELECT * FROM YYYYYY with (NOLOCK)
WHERE ZZZZZZZZ = 1

This inserts hundreds of millions of rows. I have an index on ZZZZZZZZ.

No lockout sessions. When I check sys.dm_exec_requests, it shows that the last wait type is PAGEIOLATCH_SH . I'm not sure what this means, except that it has something to do with I / O.

sys.dm_exec_sessions shows the status RUNNING, but sp_who2 shows it as SUSPENDED.

I tried to see if the table grows, but when I call sp_spaceused XXXXXX, I get the same values.

What else can I do?

UPDATE:

, -, 600 ).

?

, , ?

Answer 1

select * from sys.dm_os_waiting_tasks

?

select * 
into #t1
from sys.dm_os_wait_stats

waitfor delay '00:01'

select * 
into #t2
from sys.dm_os_wait_stats

SELECT #t2.wait_type, 
#t2.waiting_tasks_count - #t1.waiting_tasks_count as waiting_tasks_count, 
#t2.wait_time_ms- #t1.wait_time_ms as wait_time_ms, 
#t2.signal_wait_time_ms- #t1.signal_wait_time_ms as signal_wait_time_ms
FROM #t2  JOIN #t1 ON #t2.wait_type = #t1.wait_type
where #t2.wait_type not in ('CHECKPOINT_QUEUE','CHKPT','FT_IFTS_SCHEDULER_IDLE_WAIT',
'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP',
'LOGMGR_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH',
'SQLTRACE_BUFFER_FLUSH' ,
'XE_DISPATCHER_WAIT',
'XE_TIMER_EVENT', 'WAITFOR')
order by wait_time_ms desc       

Answer 2

, :

xxxxxx? .

xxxxxx, ? .

/* Before */
alter index YourIndex on xxxxxx disable
/* After */
alter index YourIndex on xxxxxx rebuild

Answer 3

? , Sys.Dm_tran_database_Transactions. , , , :

SELECT * FROM Sys.Dm_tran_Database_Transactions

MSDN artical, : MSDN

,

Answer 4

, , DW-, . , SQL Server 2008, . :

. , - .

, , , + , .

, . , .

Answer 5

DW?

http://books.google.com/books?id=eBacaL61sa4C&pg=PA268&lpg=PA268&dq=remove+index+when+updating+fact+table&source=bl&ots=MFB8HnmYRl&sig=w680N0GA3oSiJaS1mU_Z_WzW1VY&hl=en&ei=-EeOTKaQDtPyOeqZtLsK&sa=X&oi=book_result&ct=result&resnum=8&ved = 0CD4Q6AEwBw # v = onepage & q & f = false

p268