How to avoid key lookup

Question

My database needs to have GUID's as its primary key as its synced with multiple offline databases so IDENTITY column was not an option as it would lead to collisions in syncing.

Since GUID's would have lead to high table fragmentation, we opted to add another column to all our tables CREATEDDATETIME which is a timestamp and make CREATEDDATETIME as the CLUSTERED Index and GUID column has been made a NON-CLUSTERED index.

The issue is that CREATEDDATETIME is hardly if ever used in a WHERE clause, so almost all queries in their execution plan show a KEY LOOKUP on the clustered index CREATEDDATETIME to get its data. I was wondering if this performance can be improved in 1 of these 2 ways:

1. For all non-clustered indexes such as the one on GUID column I also INCLUDE CREATEDDATETIME column? OR;

2. I make every non-clustered index as a composite key where I make sure the clustered index is part of it ie GUID + CREATEDDATETIME

Which one might be better?

Answer 1

Key lookups occur when the information that you ultimately need is not available at the leaf level and so it must go to the clustered index to obtain it. Imagine the following query:

select a, b, c
from dbo.yourTable
where GUID = <some guid>;

If a, b, and c are included columns in the index, the key lookup can be avoided. Note that the clustering key is automatically an include column in every non-clustered index (which makes sense - how else would it be able to do the key lookup?). So, include columns based on what is actually being selected and I think you'll see the key lookups disappear from your query plans.