Take the 2-minute tour ×

I have a SQL 2005 x64 server, and when I attempt to issue some queries against it (for example, when I try to create an index), my SPID goes to "sleeping" immediately, and seems to wait there indefinitely. It's not being blocked (the "BLKBY" column in SP_WHO2 is empty), and the CPU and DiskIO values are very small (under 300 each), and not growing.

What could my query possibly be waiting for? If I do a SELECT * off the table I'm indexing, I get all million rows back within a minute or so, so it's not blocked table access, or even (it seems) table contention.

Any thoughts on other things I could check? Do I just need to give in and restart my SQL instance? :)

DETAILS: I'm running the CREATE INDEX from another tab in SSMS, and it's never returning - it just shows "Executing" and never returns, so I don't think the process has been abandoned.

share|improve this question

2 Answers 2

up vote 14 down vote accepted
select * 
from sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
where r.session_id = <spid of create index>;

This will show not only the status of the request, but also all the tasks spawned by the request. An online CREATE INDEX may spawn parallel threads and will suspend itself until they're finish.

share|improve this answer
excellent answer. just what I was looking for. blog.sqlauthority.com/2009/01/07/… got me going, and this showed me exactly where the problem was. –  Peter Munnings Jan 6 '12 at 12:14
Thanks just saved my bacon! –  Preet Sangha Apr 22 '13 at 5:26

The command is completed and the connection is waiting for the next command.


From the URL: "This issue is as old as SQL Server. In fact, it goes back to Sybase days but continues to fool and puzzle administrators.


share|improve this answer
But I'm running the command from SSMS, and my session still shows "Executing" - it doesn't appear to be done at all, and it went to "Suspended" way too fast to have actually completed. Interesting link, though - I was unaware of that connection. –  SqlRyan Dec 11 '09 at 20:09

Your Answer


