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.

Answer 2

Since you mentioned SQL-Azure in the note above, it is safe to say that you will have to test different approaches. You have listed 2 and there might be others depending on your application (data, query profiles, and index coverages).

As you may already know, fragmentation affects selects from inserts differently. So your app needs will dictate what choices you make. While you're optimizing for lookups your inserts might become unbearably slow.

Both logical fragmentation and physical fragmentation could affect option 1 whereas option 2 seems like a plain overhead with no clear optimization conditions (suitable for plans to use). Plan optimization techniques as shown in the Azure manuals can help there.

For fragmentation testing, I use this query that someone recommended a while back:

SELECT OBJECT_NAME (S.[object_id]) as ObjectName,
       I.name as IndexName,
       ROUND (S.avg_fragmentation_in_percent, 2) as FragPercent,
       S.page_count as PageCount,
       ROUND (S.avg_page_space_used_in_percent, 2) as PageDensity
FROM sys.dm_db_index_physical_stats 
     (DB_ID ('MyDatabase'), NULL, NULL, NULL, 'DETAILED') as S
CROSS APPLY sys.indexes as I
WHERE I.object_id=S.object_id
AND   I.index_id=S.index_id
AND   S.index_level = 0;