71

I'm trying to troubleshoot some intermittent slowdowns in our application. I've got a separate question here with more details.

I ran sp_who2 to and I've noticed a few connections that have a status of SUSPENDED and high DiskIO. Can someone explain to me what that indicates?

enter image description here

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
NullReference
  • 4,404
  • 12
  • 53
  • 90
  • 1
    See also [How to find out why the status of a spid is suspended? What resources the spid is waiting for?](http://stackoverflow.com/questions/22038695/how-to-find-out-why-the-status-of-a-spid-is-suspended-what-resources-the-spid-i) – Vadzim Dec 15 '16 at 10:31

3 Answers3

75

This is a very broad question, so I am going to give a broad answer.

  1. A query gets suspended when it is requesting access to a resource that is currently not available. This can be a logical resource like a locked row or a physical resource like a memory data page. The query starts running again, once the resource becomes available. 
  2. High disk IO means that a lot of data pages need to be accessed to fulfill the request.

That is all that I can tell from the above screenshot. However, if I were to speculate, you probably have an IO subsystem that is too slow to keep up with the demand. This could be caused by missing indexes or an actually too slow disk. Keep in mind, that 15000 reads for a single OLTP query is slightly high but not uncommon.

sXe
  • 242
  • 1
  • 4
  • 11
Sebastian Meine
  • 11,260
  • 29
  • 41
  • 5
    If the cause is a lock, shouldn't that show up in `BlkBy` or `blocking_session_id`? –  Apr 14 '14 at 21:44
  • 2
    Correct, @JonofAllTrades. If process A is trying to access a resource that is locked by process B, B's session id will show up in A's blocking_process_id column. – Sebastian Meine Apr 15 '14 at 01:40
24

Suspended. The session is waiting for an event, such as I/O, to complete.

http://msdn.microsoft.com/en-us/library/ms174313.aspx

Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
  • 6
    Which translates to the disk the database is on is being used heavily (by the database it self or other software on the server) and the database is having to wait in line for its turn to access the drive. – Scott Chamberlain Jul 23 '13 at 17:41
  • 1
    That's what I'm looking for. Are those numbers considered high for I/O? – NullReference Jul 23 '13 at 17:42
  • 1
    As I said in my answer, this interpretation is really speculation as your screen shot does not reveal what the query is actually waiting for. 15K reads for an OLTP query is slightly high but not out of range. – Sebastian Meine Jul 23 '13 at 17:45
0

Run sp_who2 to find the suspended spid's

Then right click on the server name and open "Activity Monitor"

In Activity Monitor in the Processes section, look for that spid in the "Blocked By" column

enter image description here

That will tell you which process is preventing your suspended process from running

opperman.eric
  • 314
  • 1
  • 14