Have you ever encountered a slow-running query on your SQL Server? In today’s blog post, we will discuss a common performance issue and a solution to improve query performance without modifying the query itself.

During a recent Comprehensive Database Performance Health Check, I came across a client’s server with a very expensive query. Despite having an index seek as the main operator on the table, the query was running extremely slow. The client requested not to modify the query, so we had to find an alternative solution.

Upon analyzing the execution plan, I noticed that along with the index seek, there was a bookmark lookup operation. This bookmark lookup was causing the performance bottleneck. To resolve this issue, we needed to eliminate the bookmark lookup.

To demonstrate this, I have created a similar query using the sample database WideWorldImporters. Let’s run the query with the execution plan and statistics IO enabled:

SET STATISTICS IO ON

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

After running the query, we can see the statistics IO output:

Table 'Orders'. Scan count 1, logical reads 415.

Now, let’s remove the bookmark lookup by creating a nonclustered index on the ContactPersonID column:

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

After creating the index, let’s run the query again and check the statistics IO:

Table 'Orders'. Scan count 1, logical reads 2.

As you can see, by removing the bookmark lookup through the use of an index, we were able to significantly improve the query performance. This is just one method to optimize your queries in SQL Server.

Remember, there are various techniques and approaches to tune SQL Server for better performance. If you’re facing performance issues, consider reaching out for a Comprehensive Database Performance Health Check to identify and resolve any bottlenecks.

Stay tuned for more SQL Server tips and tricks!