What is suspend status of a SPID?
All of us experienced that a SPID is suspended and probably wondered to know if the SPID is really doing anything. Personally I see this anxiety more in developers if they have access to see the status of SPID in the server.
Suspend status of a SPID means it's either waiting on I/O or it has a wait for clause in the execution of job/query which is causing the session to go into suspended mode.
However SPID in suspend status not necessary means it is a bad news. Look if SPID is consuming memory/IO and if batch time is changing normally. Typically is these values changes as expected then no action is needed and we can conclude the SPID is working fine.
Otherwise we may need to kill SPID and take best decision on the situation. For example, if it waits with WAITFOR status for ever and then we need to kill SPID, we need to figure it which part of the code placed this SPID in WAITFOR clause for ever and then fix that code before we can resolve this issue.
However, after a SPID is killed, we need to actually do a root cause analysis to figure out why it actually failed and then we should resolve the issue.
Suspend status of a SPID means it's either waiting on I/O or it has a wait for clause in the execution of job/query which is causing the session to go into suspended mode.
However SPID in suspend status not necessary means it is a bad news. Look if SPID is consuming memory/IO and if batch time is changing normally. Typically is these values changes as expected then no action is needed and we can conclude the SPID is working fine.
Otherwise we may need to kill SPID and take best decision on the situation. For example, if it waits with WAITFOR status for ever and then we need to kill SPID, we need to figure it which part of the code placed this SPID in WAITFOR clause for ever and then fix that code before we can resolve this issue.
However, after a SPID is killed, we need to actually do a root cause analysis to figure out why it actually failed and then we should resolve the issue.
Comments