Systems Engineering and RDBMS

Archive for March 28th, 2007

Bookmark Look-up Operator

Posted by decipherinfosys on March 28, 2007

A bookmark look up operator in a SQL Server execution plan indicates that an index was used for the retrieval of the data and then a bookmark was then used to look up the data in the clustered index or the table to look up more data in order to suffice the query requirements.  This look-up is used typically to retrieve columns that are in the select statement that were not in the index.  This means that at-least twice the I/O is necessary in order to suffice the query.  If the original index leading to the bookmark is selective enough, then having a bookmark look-up is not an issue, however if the original index does not filter down the data to a less result set, then this bookmark look-up can cause sever performance issues which becomes obvious in case of large tables since the data is typically spread across several pages in that case.

In SQL Server 2005, a bookmark look-up is not used.  A clustered index seek operation and a RID (Row-ID) look-up is used instead and if you have Service Pack 2 installed, then the Key Look-up operator also provides the same functionality.

Posted in SQL Server | Leave a Comment »