Remove Bookmark Lookup – SQL in Sixty Seconds #089

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.

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.

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)