This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally it appears to get stuck.

I have looked at the results of sp_who2 and what I see is that the process is not blocked, it's still listed as 'RUNNABLE', but the value in disk IO stays the same. Once the process gets in this state it never finishes. If I kill the process and re-execute the stored proc, I usually get the same result.

Sometimes re-executing the stored proc works, but increasingly I end up having to restart SQL Server. Not ideal... After restart the stored proc executes as expected. Has anyone even hit a problem like this? Any advice would be greatly appreciated.

Some more context. The stored proc I am executing looks something like this:

from table1

insert into table1
select columns
    select columns
    from giant_table_1 WITH (NOLOCK)
    where condition
    group by columns


    select columns
    from giant_table_2 WITH (NOLOCK)
    where condition
    group by columns
where condition

more crud on table1

If I insert some logging statements into the stored proc I can see that when it hangs it hangs on the insert/select. Wondering if this could be a weird consequence of the NOLOCK statement. We (and by we I mean my boss :)) added the nolock because giant_table_1 and giant_table_2 are constantly being changed. Without the nolock our sproc was getting blocked a lot by other long running processes and vice versa.

Thanks in advance!

| improve this question | | | | |
  • Currently working to split the insert/select statement into 2 separate inserts to see if that provides any more clues into where it's hanging up. – Ben Dec 23 '11 at 16:30
  • What does the execution plan look like? Anything in sys.dm_os_waiting_tasks? – Martin Smith Dec 23 '11 at 16:35

From the looks of your query I believe that the process is waiting on a spill to tempdb. You will need to look at the output of

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <SPID for the SP>

to get a better idea of whats occuring in the background, Adam Machanic's sp_whoisactive is a great tool for simplifing the collection of details about a queries execution.

Also make sure that autogrowth is set correctly for your database and tempdb with sensible auto growth amounts for the data and log and that the number of VLFs in the log files is under a 100. Look at his post for details 8 Steps to better Transaction Log throughput

| improve this answer | | | | |
  • Thanks for the response! It may be a few days before I see this issue show up again. But I'll defiantly try your suggestion when it does. – Ben Dec 23 '11 at 17:57

I haven't had this issue again since refactoring my stored procedure to have 2 inserts instead of one big union'd insert. It's kind of questionable but I don't know what else to call the answer here. Thanks everyone for your suggestions.

| improve this answer | | | | |
  • Remember that a UNION actually performs a uniqueness check between the two sets, there is a lot of overhead work there that could definitely explain the query running away. If you know the two groups won't have duplicate rows (or you don't care if you have duplicate rows), then use UNION ALL, or (as you did), split it into two separate inserts. – BradC Mar 28 '17 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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