Post reply

Bookmark Lookup

  • September 3, 2008 at 10:56 pm

    #131015

    Hi All,

    I have gone through many article on Bookmark Lookup but still now it is not clear to me. What exactly Bookmark Lookup mean? Could you please explain me in pure english?

    Thanks in advance.

    Regards

    Arijit

  • September 4, 2008 at 2:24 am

    #866987

    http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

    http://sqlinthewild.co.za/index.php/2008/08/28/an-example-exec-plan/

    In essence, it's a single row seek to either the clustered index or the heap to get columns that are needed, but not present in the nonclustered index that was used in the query.

    Does that clear anything up?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 4, 2008 at 10:10 pm

    #867552

    Thanks a bunch Gail..

  • September 5, 2008 at 12:12 am

    #867572

    This is what actually happens with Bookmark lookup mechanism:

    When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not contained in the non-clustered index, SQL Server must go back to the data pages to obtain the data in those columns.

    Thus Bookmark lookups require data page access in addition to the index page access needed to filter the table data rows. Because this requires the query to access two sets of pages instead of only one, the number of logical READS performed by the query will increase.

    These additional requirements for logical READS and physical I/O can cause bookmark lookups to become quite costly for large result sets.

    Hope you get something useful from this description. 🙂

  • September 5, 2008 at 12:36 am

    #867583

    Now it is more clear..Thanks a bunch for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply