June 19, 2008 at 5:53 am
Hi everyone,
I want to know what is bookmark lookup in execution plan.
And can I remove from execution plan to see the better performance in
query?
Thanks,
Sunil
June 19, 2008 at 6:13 am
You can't just change the Execution plan to use something ele you have to hcnage something like your code, create a new index for the query or similar...
this article will give you some further unerstanding of Query Execution plans
http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx
Gethyn Elliswww.gethynellis.com
June 19, 2008 at 6:16 am
A bookmark lookup means an index was used and once the value was found, the actual record needed to be "looked up" in either the clustered index or the heap if you do not have a clustered index.
They can be avoided in three ways.
A table scan will avoid a bookmark lookup - of course, the table scan will probably slow things down so this is typically not what you want.
If your query used the clustered index to satisfy it's conditions, it would not have to do a lookup because the record is part of the index. Sometimes this can be good.
If your index included all of the columns your query needed for both lookup and for returning data, the query would not need to go back to the table to get anything and would use the data in the index to satisfy the query. This would mean either adding additional columns to the index or to the leaf level of the index (INCLUDE). In many cases, this can be a good plan, but it can makes indexes larger and have a greater potential of being impacted by updates.
Now, back to your issue - if your bookmark lookup is taking a significant portion of time in your query, you may have a poorly selected clustered index, no clustered index at all, or a fragmented clustered index. You should look into these right away.
June 19, 2008 at 6:25 am
Bookmark lookups occur when SQL uses a nonclustered index to evaluate the rows required for a query. If the nonclustered index does not contain all the columns needed for the query (in select, from and where clauses), then a bookmark lookup is necessary to fetch the rest of the columns from the clustered index or heap.
To remove bookmark lookups, you need to ensure that the columns required for the query are all in the nonclustered index. depending on the query and the table structure, that may be easy, or it may be impossible.
I assume you're using sQL 2000, since bookmark lookup have been renamed in sQL 2005. Is that the case?
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
June 19, 2008 at 7:14 am
Hi ,
I am using sql 2005.
June 19, 2008 at 7:22 am
Main difference between 2000 and 2005 is that in 2005 you can alse add columns as INCLUDE columns insted of as part of the key. Makes it a lot easier to cover queries.
If you want some specific advice for this query, please post the execution plan in xml format (saves as a .sqlplan file, zipped and attached to your post)
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply