SQL Server - Why would my SPID be "SUSPENDED" but not blocked, while creating an index?

sql-server suspend
SqlRyan picture SqlRyan · Dec 11, 2009 · Viewed 43.7k times · Source

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.

Answer

Remus Rusanu picture Remus Rusanu · Dec 11, 2009 
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.

Related questions

Suspended status in SQL Activity Monitor

What would cause a query being done in Management Studio to get suspended? I perform a simple select top 60000 from a table (which has 11 million rows) and the results come back within a sec or two. I change the query …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More