How can I tell why my query is running so slow?

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 on stackoverflow, 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

The query is running, the PAGEIOLATCH_SH is typical when doing a lot of inserts as it is a wait on the disk subsystem. The index is actually hindering performance as the index gets an insert for every insert to the table.

100's of millions are a lot of records and will take some time, how long has it been running?

Answer 2

If you are using 2005 or 2008, I would definitely run the sp_whoisactive script developed by SQL MVP Adam Mechanic. It's basically sp_who/sp_who2 on steroids and should give you all the info you need.

http://sqlblog.com/files/folders/beta/entry27502.aspx