How can I tell why my query is taking so long?

Question

I have a query that is taking a lot longer then usual, and I cannot tell if it is stuck.

The query is something like this:

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

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

There are no blocking 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 is RUNNING, but sp_who2 shows it as SUSPENDED.

I tried to see if the table is growing, but when I call sp_spaceused XXXXXX, I keep getting the same values.

What else can I do?

UPDATE:

With the help of the answers below, I have found that there is an I/O issue, and that my query is resulting in an average of about 600 records being inserted per minute).

What is my next step?

What can I do before I start to assume that my disk is going bad?

Answer 1

If you try the following

select * from sys.dm_os_waiting_tasks

does the resource address it's waiting on change at all?

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

Does the row count value of the table X property sheet show changes?

Answer 3

Some thoughts that might help with the insert:

Are there any insert triggers on xxxxxx? Those could have a significant impact on a large insert operation.

Are there non-clustered indexes on xxxxxx that could be disabled during the load? That would also go a long way towards helping.

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

Answer 4

Is the insert inside of a transaction? If it is, you could try to check the transaction details inside Sys.Dm_tran_database_Transactions. It shows the current number of entries writen to the transaction log along with some other health stats that should be changing over time:

SELECT * FROM Sys.Dm_tran_Database_Transactions

This is a link to the MSDN artical that explains the columns: MSDN Column documentation

Hope that helps

Answer 5

Ok, sounds like you may be in a DW-style environment, moving lots of data from one table to another. Assuming you are on SQL Server 2008, see this whitepaper:

The Data Loading Performance Guide

See the sections on minimal logging, and further down on partition switching.

It helps to read the whole paper through a few times, so you really grok what's going on beneath the covers, and why certain combinations of data + indexing work and others don't.

Partition switching makes minimal logging easy to achieve, since it gives you an empty target table, and allows new data to come on-line in an instant, once the load has finished. Might need enterprise edition, though.

Answer 6

DW Fact table?

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