Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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?

share|improve this answer
    
The index is on YYYYYY. Not XXXXXX. The insert used to take about 15 hours, but it is going on 26 now. –  Gabriel McAdams Sep 10 '10 at 19:24
    
My mistake, there are no indexes on XXXXXX? –  Dustin Laine Sep 10 '10 at 19:29
    
Also, I like using Activity monitor to watch the process. –  Dustin Laine Sep 10 '10 at 19:37
    
There are no enabled indexes in XXXXXXX. There is a non clustered index, but I disable it before the insert. Again, though, this insert is going a lot slower than it used to. That is what I am trying to solve. –  Gabriel McAdams Sep 11 '10 at 17:00

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

share|improve this answer
    
Thank you for this. I actually have an earlier version that I am using already. I just don't know all that much about how to read it. –  Gabriel McAdams Sep 10 '10 at 19:51
    
fair enough. As a starting point, I would check it a couple minutes apart and make sure the read/write counters are increasing. At least you will be able to tell whether it's actually going or stuck. –  SQL3D Sep 10 '10 at 20:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.