How to get bookmark lookups by parsing execution xml

Last Post 02 May 2012 12:09 PM by gunneyk. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
e90fleet
New Member
New Member

--
28 Apr 2012 08:37 PM
Hi guys,

i did a lot of research online and checked XML for the stored procedure with bookmark lookups, but i am not able to see how i can identify bookmark lookups in XML.
Does anyone know how i can do that?
What i am trying to do is to try to identify all stored procedures with bookmark lookups.
Maybe there are another way other by searching XML?

Thanks
rm
New Member
New Member

--
29 Apr 2012 09:13 AM
You are looking at XML format of execution plan?
e90fleet
New Member
New Member

--
29 Apr 2012 09:38 AM
i would like to search through the XML format.
e90fleet
New Member
New Member

--
29 Apr 2012 09:39 AM
i would like to search through the XML format.
rm
New Member
New Member

--
30 Apr 2012 06:02 AM
How did you generate execution plan?
e90fleet
New Member
New Member

--
30 Apr 2012 02:22 PM
just by executing the stored procedure
rm
New Member
New Member

--
01 May 2012 05:23 AM
Did you save execution plan to file or just look for it in dm view?
gunneyk
New Member
New Member

--
01 May 2012 06:13 AM
Bookmark Lookup is a pre-2005 term. You can find info on it here http://blogs.msdn.com/b/craigfr/arc...52639.aspx . In 2005 and later it shows up as a nested loop join and uses a Clusered Index Seek with a RID lookup. But I would have to ask why you want to find all these? You can't get rid of all bookmark lookups and don't fall into the myth that they are all evil. They are simply necessary in many cases. You would be better spending your time looking for plans that are overall ineffecient regardless of the reason. Choose the top x ones that get called the most in combination with the worts resource usage and tune them.
e90fleet
New Member
New Member

--
01 May 2012 08:53 PM
Thank you for reply. The reason why i want to reduce the bookmark lookups is because i noticed that it increases cpu on the server. i will try to reduce at least some of them.
gunneyk
New Member
New Member

--
02 May 2012 12:09 PM
Increases CPU over what? The only way to get rid of a bookmark lookup is to either use a clustered index or have a totally covering non-clustered index. Since you can only have 1 clustered index on a table if you lookup data more than one way (which is almost always the case) the other lookups must eiterh be covered indexes (which is impractical for all of them) or you simply get a bookmark lookup. So again you should really be looking for high CPU, high reads etc as the motive regardless of what the operation is vs. just being concerned with bookmark loolups.
You are not authorized to post a reply.

Acceptable Use Policy