Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/260652

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 17:20, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH" May Hold Locks Longer

Article ID: 260652

Article Last Modified on 10/17/2003


  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q260652


If an execution plan uses a Nested loop join and a bookmark lookup, which uses the WITH PREFETCH clause, the locks are held for the duration of the nested loop joins determining the qualifying bookmarks. After the bookmark is used to fetch the outstanding column data and that data is placed in the output buffer, the lock is then released. This behavior may lead to the blocking of other system process ids (spids).


If you change the join hint from LOOP to either HASH or MERGE, the blocking does not occur.


This behavior can be illustrated by using the following example:

SELECT c.mstr_acct, c.last_name_m, c.first_name, c.name_prefix, c.company_name
FROM tickler a 
 INNER LOOP JOIN member b    ON a.mbr_acct = b.mbr_acct 
 INNER LOOP JOIN person c    ON b.mstr_acct = c.mstr_acct 
WHERE a.tickler_code = 'SPIRITEXT' 

The SHOWPLAN output for the preceding query follows:

  |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([TestLoopJoin].[dbo].[person] AS [c]) WITH PREFETCH)
       |--Nested Loops(Inner Join)
            |--Nested Loops(Inner Join)
            |    |--Index Seek(OBJECT:([TestLoopJoin].[dbo].[tickler].[tickler_code] AS [a]), SEEK:([a].[tickler_code]='SPIRITEXT') ORDERED)
            |    |--Clustered Index Seek(OBJECT:([TestLoopJoin].[dbo].[member].[pk_member] AS [b]), SEEK:([b].[mbr_acct]=[a].[mbr_acct])  ORDERED)
            |--Index Seek(OBJECT:([TestLoopJoin].[dbo].[person].[pk_person] AS [c]), SEEK:([c].[mstr_acct]=[b].[mstr_acct]) ORDERED)

The locking behavior is readily observed by starting another connection that performs an UPDATE on the first few rows being selected from the table. You will note that the UPDATE is blocked until all the bookmark lookups for the SELECT statement are processed and the rows are sent to the client. Bookmark Lookup

The Bookmark Lookup logical and physical operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, then the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.

Keywords: kbprb kbpending KB260652