CODEWITHSUNDEEP
sql-serversuspend
SqlRyan
SqlRyan

Reputation: 33924

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

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.

Upvotes: 11

Views: 50697

Answers (3)

marknuzz
marknuzz

Reputation: 2942

The Suspended state can sometimes be misleading. For example, your query could be Suspended while waiting for disk I/O to complete. This can be verified by running the below query and checking the wait_type column. PAGEIOLATCH_EX indicates that the query is blocked due to waiting for disk I/O. This doesn't mean that the query is not making progress.

See this page for more information about PAGEIOLATCH_EX

And here is the query that returns the aforementioned information

 SELECT qs.percent_complete ,
        qs.session_id ,
        scheduler_id ,
        blocking_session_id ,
        qs.status ,
        command ,
        wait_time ,
        wait_type ,
        last_wait_type ,
        wait_resource ,
        ST.text ,
        host_name ,
        program_name
 FROM   sys.dm_exec_requests qs
        LEFT JOIN sys.dm_exec_sessions es ON ( qs.session_id = es.session_id )
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS ST

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294387

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.

Upvotes: 21

Darryl Peterson
Darryl Peterson

Reputation: 2270

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

http://blogs.msdn.com/psssql/archive/2008/04/21/how-it-works-what-is-a-sleeping-awaiting-command-session.aspx

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.

A session with that status of sleeping / awaiting command is simply a client connection with no active query to the SQL Server. The table below shows the transitions from running to sleeping states for a session."

Upvotes: -2

Related Questions