SQL Server Performance Forum – Threads Archive
Locking – By Same Process ID – itself!
If a process is locked by itself (i.e. SELECT process) – how can this be resolved??KBAhttp://support.microsoft.com/default.aspx/kb/906344 to fix it. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Only option might be upgrading to faster disk system…
Article provided by Satya explains it but it not the solution…
MS introduced the new feature for troubleshooting in SP4…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
It is nothing a shared latch request is put on for the page. This won’t run until the first latch request is complete i.e. the page is returned and therefore the spid sleeps. When the page is returned the exclusive latch is released allowing the shared latch to be given and thus the process the continues, resulting in synchronous IO. Also it is better to keep the transactions in smaller batch to avoid such self-blocking. Also you need to see whether the query is blocking is a resource-intensive or not. I would suggest to update statistics with full scan or run dbcc dbreindex. This was the explanation from MSFT in other case
quote:
In SP4 we are able to determine the
blocking spid for latch waits when the blocking spid holds the latch in
exclusive or update mode. In these cases the blocking spid information is
populated into the "blocked" field in sysprocesses.
So why is the spid blocked by itself? Well, that has to do with how latches
are used for IO operations. When an IO is issued for page, a latch is held
on the page. Depending on the IO operation, the latch mode acquired is
shared (SH) or exclusive (EX). The latch for the IO is acquired by the
thread that issues the IO. Since all SQL Server IO operations are
asynchronous, if the spid that issued the IO wants to wait for the IO to
complete it will attempt to acquire another latch on the same page after
issuing the IO. The first latch is released when the IO completes. This
release allows the second latch request to be granted.
Here’s an example of how this works:
1. Spid 55 wants to read page P1 which does not exist in the buffer pool.
2. Spid 55 acquires an EX latch on page P1 — this marks spid 55 as owning
the latch. The latch is in an in memory data structure, not the physical
page itself. Since the page does not yet exist in memory the mode is EX in
order to force other spids that may also want to access the page to wait for
the IO to complete and also to prevent them from issueing a second IO
operation for the same page.
3. Spid 55 issues the IO request to read P1 from disk.
4. Since Spid 55 wants to read the page, it must wait for the IO to
complete. It does this by attempting to acquire another latch (in this case
a share (SH)) latch on the page. Since the latch is already held in EX, the
SH request is blocked and the spid is suspended.
5. Upon completion of the IO the EX latch on the page is released.
6. The release of the EX latch grants the SH latch to spid 55.
7. Spid 55 can now read the page. For the duration between steps 4 (the SH latch acquire) and step 5 (EX latch
release) sysprocesses will indicate that spid 55 is blocked by itself with a
wait type of PAGEIOLATCH_XX (where XX can be SH, UP, or EX) as an indication
that it is waiting for the completion of an IO that it itself issued.
In SP4 we are able to determine the
blocking spid for latch waits when the blocking spid holds the latch in
exclusive or update mode. In these cases the blocking spid information is
populated into the "blocked" field in sysprocesses.
So why is the spid blocked by itself? Well, that has to do with how latches
are used for IO operations. When an IO is issued for page, a latch is held
on the page. Depending on the IO operation, the latch mode acquired is
shared (SH) or exclusive (EX). The latch for the IO is acquired by the
thread that issues the IO. Since all SQL Server IO operations are
asynchronous, if the spid that issued the IO wants to wait for the IO to
complete it will attempt to acquire another latch on the same page after
issuing the IO. The first latch is released when the IO completes. This
release allows the second latch request to be granted.
Here’s an example of how this works:
1. Spid 55 wants to read page P1 which does not exist in the buffer pool.
2. Spid 55 acquires an EX latch on page P1 — this marks spid 55 as owning
the latch. The latch is in an in memory data structure, not the physical
page itself. Since the page does not yet exist in memory the mode is EX in
order to force other spids that may also want to access the page to wait for
the IO to complete and also to prevent them from issueing a second IO
operation for the same page.
3. Spid 55 issues the IO request to read P1 from disk.
4. Since Spid 55 wants to read the page, it must wait for the IO to
complete. It does this by attempting to acquire another latch (in this case
a share (SH)) latch on the page. Since the latch is already held in EX, the
SH request is blocked and the spid is suspended.
5. Upon completion of the IO the EX latch on the page is released.
6. The release of the EX latch grants the SH latch to spid 55.
7. Spid 55 can now read the page. For the duration between steps 4 (the SH latch acquire) and step 5 (EX latch
release) sysprocesses will indicate that spid 55 is blocked by itself with a
wait type of PAGEIOLATCH_XX (where XX can be SH, UP, or EX) as an indication
that it is waiting for the completion of an IO that it itself issued.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>