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 |
Resolution : If
1. The value in the waittime column is low.
2. The waittype of the SPID is a latch waittype.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.
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
No comments:
Write commentsPlease do not enter spam links