Remove Bookmark Lookup – SQL in Sixty Seconds #089

Remove Bookmark Lookup - SQL in Sixty Seconds #089 89-RemoveBookmarkLookup-800x450 Just another day during the Comprehensive Database Performance Health Check, I identified a very expensive query on my client’s server. We fixed the query performance by removing the bookmark lookup. Let us learn about that in today’s blog post.

The query was indeed running extremely slow on my client’s machine even though the main operator on the table was index seek. When we looked at the query, the query indeed needed a bit of re-write however, the condition from the client was not to modify any query. In that case, one of the options was that we tune query with the help of the index.

Looking a little bit deeper on the execution plan, one of the table operators was doing Index seek. However, along with that, there was a bookmark lookup. I quickly realized that fixing the bookmark lookup will solve the problem.

I have created a similar query based on the original troubling query, where I have used the sample database WideWorldImporters.

Now, when you run the query with the enable execution plan and also with the statistics IO on, you will see the following details.

Run the following command to enable statistics for the IO.

SET STATISTICS IO ON

Now enable the execution plan.

SELECT [OrderID],[ContactPersonID],
		[PickingCompletedWhen]
FROM [WideWorldImporters].[Sales].[Orders]
WHERE ContactPersonID = 3176;

Here is the output of the statistics IO.

Table ‘Orders’. Scan count 1, logical reads 415.

Here is the execution plan.

Remove Bookmark Lookup - SQL in Sixty Seconds #089 removebookmark1

Now continue watching the SQL in Sixty Seconds video. In the video, I will explain how you can remove bookmark lookup and get performance immediately for the query.

The index used in the query which will remove the bookmark lookup is here.

CREATE NONCLUSTERED INDEX [FK_Sales_Orders_ContactPersonID] ON [Sales].[Orders]
([ContactPersonID] ASC) INCLUDE([PickingCompletedWhen]);

Once you run the index, it will give you the following statistics IO output.

Table ‘Orders’. Scan count 1, logical reads 2.

Here is the execution plan.

Remove Bookmark Lookup - SQL in Sixty Seconds #089 removebookmark2

Well, that’s it for today. Remember this is just one method to help tune your query. There are many different methods to tune SQL Server, which we usually work together during the Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
Identify Index Used for Query – SQL in Sixty Seconds #088
Next Post
Creating Covering Index to Eliminate Join – SQL in Sixty Seconds #090

Related Posts

2 Comments. Leave new

  • Valentin Petkov
    July 22, 2020 6:06 pm

    Nice , Thank you the only I did not figure out
    SELECT TOP 1 lead(price, 7) OVER (ORDER BY [oDateTimeLocal] DESC)
    FROM Price
    WHERE [month] <= 3)

    — WHEN WE HAVE ORDER BY oDateTimeLocal needs to be in "WHERE" index or Include index
    I have not too much data and still can not catch that index

    to recap ORDER BY I'm interested should be included somewhere?

    Thanks
    Valentin

    Reply
    • It is difficult to say without understanding the execution plan but it is safe to go with the included column.

      Reply

Leave a Reply

Menu