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?
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?