Bookmark Lookup


I'm trying to optimise a stored procedure in SQL 2000 and in the Query Execution Plan the highest Query Cost is on a Bookmark Lookup, however all the information I have looked at says to create an index to resolve this but the script is two tables joined so am a little stuck as to how to resolve.

Edit **

Here is the information from the query execution plan, as you can see it mentions one table but in the output it mentions the joined table too.

alt text

All ideas gratefully received.

Many thanks

more ▼

asked Aug 26, 2011 at 02:27 AM in Default

avatar image

5.3k 66 69 77

Table and index scripts along with the query please :o)

Aug 26, 2011 at 02:29 AM WilliamD

Sorry, having trouble getting something that will make sense.

Aug 26, 2011 at 03:22 AM Mrs_Fatherjack

That part of the execution plan just tells us you have the bookmark lookup, maybe you can supply us with the table & index creation scripts along with the actual t-sql query of the sproc.

Aug 26, 2011 at 03:43 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

To create a covering index in SQL SErver 2000, you have to add ALL columns from the output to the key. The only ones you don't have to add, are the column (or columns) that makes up the clustered index on that table (they'll be included automatically anyway). From the index above, you're missing the sProductName column and the uGUID.

more ▼

answered Aug 26, 2011 at 05:11 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

Thanks, I'm not sure how do this though as the sProductName and the uGUID are in a different table.

Aug 26, 2011 at 05:35 AM Mrs_Fatherjack

Are they the clustered key? Then you don't need to include them. But, you should have seen a different plan if they are.

Aug 26, 2011 at 05:39 AM Grant Fritchey ♦♦

Also, the tool tips are nice, but get down to the properties to really see good info.

Aug 26, 2011 at 05:40 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Even though it is 2 tables joined, the bookmark lookup should just be against one object.

more ▼

answered Aug 26, 2011 at 02:32 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Have added the query execution plan information regarding the bookmark lookup for your thoughts. Many thanks

Aug 26, 2011 at 03:23 AM Mrs_Fatherjack

Put a covering index on SLCL for iFormID, iCheckedByID, iProdID and see what effect that has

Aug 26, 2011 at 03:38 AM Kev Riley ♦♦

It's not made any difference. This is what I did:


 /****** Object:  Index [IX_SPHINX_linkCustomerLink_4]    Script Date: 08/26/2011 11:33:56 ******/ 
 CREATE NONCLUSTERED INDEX [IX_SPHINX_linkCustomerLink_4] ON [dbo].[SPHINX_linkCustomerLink] 
     [iProdID] ASC,
     [iFormID] ASC,
     [iCheckedByID] ASC
Aug 26, 2011 at 03:41 AM Mrs_Fatherjack

and the execution plan was the same with the same properties on the bookmark lookup?

Aug 26, 2011 at 03:58 AM Kev Riley ♦♦

Yes, it was identical.

Aug 26, 2011 at 04:01 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 26, 2011 at 02:27 AM

Seen: 1629 times

Last Updated: Aug 26, 2011 at 03:22 AM

Copyright 2018 Redgate Software. Privacy Policy