 |
Newsgroups: microsoft.public.sqlserver.server
From: "Santeri Voutilainen [MSFT]" <sante...@online.microsoft.com>
Date: Thu, 19 May 2005 11:42:05 -0700
Local: Thurs, May 19 2005 2:42 pm
Subject: Re: SP4 reports queries blocking on themselves?
What you are seeing is not related to locks, rather it is related to the
latch waits you see in sysprocesses. 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 -- This posting is provided "AS IS" with no warranties, and confers no rights. > Adrian,
> Thanks for your reply. > I'm currently working on updating the stats for my big tables. I have > Here is an example of sp_locks and sysprocesses output for a query that > sp_lock: > sysprocesses > Microsoft SQL Server Analysis > 2104 SELECT > 000D601CAB48 TCP/IP analysis > 0x0000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000 > Appreciate your help. > -- J >> If you haven't done it already, update statistics with full scan or run >> Adrian >> "Jonathan Levine" <my...@nospam.nospam> wrote in message >> > I've noticed that several heavy queries that worked fine in SP3a now >> > SPID Status Login BlkBy Command >> > sp_lock reports that the process has an exclusive lock on an extent in >> > Has anyone else seen anything like this? >> > Regards, >> > Jonathan You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||