Why SQL Server SPID is blocking itself

Hi All

Issue: SPID is blocking itself

Description: On one of our server SQL 2005 i have run one maintainence (update statistics)job in morning hours on a businessday and as it should not create any issues i was monitoring server health. After executing job i found spid 64(belongs to job)  is blocking itself.and after some time it went off

 Output of sys.sysprocesses  (only required columns are extracted)



Output of sys.sysprocesses

 

Resolution : If

1. The value in the waittime column is low.

2. The waittype of the SPID is a latch waittype.

 you can ignore same.

 Killing spid is never be good option beacause if you kill we will get message "SPID 64: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds"

Actually it goes into a ROLLBACK Situation and stay right there.It gets converted into a Phantom Process, which can only be freed once you recycle your SQL Server Service

Reason : When an SPID is waiting for an I/O page latch, you may notice that the blocked column briefly reports that the SPID is blocking itself.

 Latches are used to synchronize access to cached data pages and other in-memory objects. from SQL Server 2000 SP4 onwards a diagnostics is added to help troubleshoot cases in which an SPID waits a long time for a latch. These diagnostics cause the blocked column in the sysprocesses system table. Before SQL Server 2000 SP4, the blocked column was only populated when a lock wait caused blocking.

 (Latch ownership is only tracked for latches that are in exclusive (EX) or update (UP) latch mode. Ownership is not tracked for latches that are in shared (SH) latch mode)

If waittype

Example: This happens in below fashion

1.SPID 64 wants to read a data page that does not exist in the buffer pool.
2.SPID 64 acquires an EX latch on the page. Because the page does not exist yet in memory, the requested latch mode is EX. The EX latch mode forces other SPIDs that may also want to access the page to wait for the I/O request to finish. The EX latch mode also prevents other SPIDs from issuing a duplicate I/O request for the same page.
3.SPID 64 issues the I/O request to read the page from disk.
4.Because SPID 64 wants to read the page, SPID 64 must wait for the I/O request to finish. To wait for the I/O request to finish, SPID 64 tries to acquire another latch that has the shared (SH) latch mode on the same page. Because an EX latch has already been acquired, the SH latch request is blocked, and the SPID is suspended. Because the EX latch that blocks the SH latch request was also acquired by SPID 64, the SPID is temporarily reported as blocking itself.
5.When the I/O request finishes, the EX latch on the page is released.
6.The release of the EX latch gives the SH latch to SPID 64.
7.SPID 64 can now read the page.

Note : Status = suspended : The SPID is waiting for an event, such as a lock or a   latch.
           Status = runnable  :  TheSPID is in the runnable queue of a scheduler and waiting to get scheduler   time.

Refrence link : http://support.microsoft.com/kb/224453