DaveWentzel.com            All Things Data

Key Lookups


Bookmark Lookup/Key Lookup
A bookmark lookup uses an index to limit a table, then the bookmark is used to probe back into the table or one of its index to get add'l cols needed to satisfy the request.  This means extra IO is needed.  Basically the nonclustered index isn't covering. 
Bookmarks aren't always bad, but the extra I/O can lead to problems. 
In 2005 SP2 this is seen as Key Lookup. 
Indexes with the INCLUDE attribute can also help here if you are running 2005. 
--Is a row id or clustering key used to lookup a row in a table or clustered index. 
--Argument column tells you the label (similar to the name) of the bookmark and the name of the table or CI being looked up
--If WITH PREFETCH is displayed then asynchronous prefetching can be used (read-ahead).  This happens specifically for a bookmark that is pulling back many rows, ie, is not very selective.  You want to make sure it doesn't span across too many pages.  If you do see PREFETCH then your transaction isolation level was likely bumped up to REPEATABLE READ for that portion of the query.  This can of course lead to lock escalation deadlocks
--Performance ramifications:
     --Try to avoid these when you see them pop up.  How?
     --Covering indexes on large tables will eliminate bookmark lookups, hence, reducing I/O.  This also resolves the lock escalation issues. 
     --WITH PREFETCH is always better if you can’t cover
     --For small tables a bookmark lookup (or even table scan) probably will be just as fast as a covering index.  Compare I/Os to determine.
     --A bookmark in an execution plan indicates that an index was used to limit the table and that a bookmark was then used to probe the clustered index or the heap table to retrieve more data that is not available in the index. A bookmark is often used in this way to retrieve columns that are in a SELECT clause. This means that at least twice the I/O is necessary to retrieve the results.
A bookmark lookup/key lookup is always followed by a Nested Loops operator that will combine the results of the two operations. 
Find Key Lookups with dm_db_index_usage_stats
don't run this on a production server
DECLARE @plans_in_cache TABLE 
query_text NVARCHAR(MAX) , 
o_name SYSNAME , 
execution_plan XML , 
last_execution_time DATETIME , 
execution_count BIGINT , 
total_worker_time BIGINT , 
total_physical_reads BIGINT , 
total_logical_reads BIGINT 
) ; 

DECLARE @lookup_objects TABLE 
table_name SYSNAME , 
index_name SYSNAME , 
index_cols NVARCHAR(MAX) 
    ) ; 

WITH    query_stats 
AS ( SELECT   [sql_handle] , 
[plan_handle] , 
MAX(last_execution_time) AS last_execution_time , 
SUM(execution_count) AS execution_count , 
SUM(total_worker_time) AS total_worker_time , 
SUM(total_physical_reads) AS total_physical_reads , 
SUM(total_logical_reads) AS total_logical_reads 
FROM     sys.dm_exec_query_stats 
GROUP BY [sql_handle] , 
INSERT  INTO @plans_in_cache 
( query_text , 
o_name , 
execution_plan , 
last_execution_time , 
execution_count , 
total_worker_time , 
total_physical_reads , 
SELECT /*TOP 50*/ 
sql_text.[text] , 
CASE WHEN sql_text.objectid IS NOT NULL 
THEN ISNULL(OBJECT_NAME(sql_text.objectid, 
ELSE CAST('Ad-hoc\Prepared' AS SYSNAME) 
END , 
query_plan.query_plan , 
query_stats.last_execution_time , 
query_stats.execution_count , 
query_stats.total_worker_time , 
query_stats.total_physical_reads , 
FROM    query_stats 
CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle) 
AS [sql_text] 
CROSS APPLY sys.dm_exec_query_plan(query_stats.plan_handle) 
AS [query_plan] 
WHERE   query_plan.query_plan IS NOT NULL ; 

DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' 
lookups AS ( 
SELECT  DB_ID(REPLACE(REPLACE(keylookups.keylookup.value('(Object/@Database)[1]', 
'sysname'), '[', ''), 
']', '')) AS [database_id] , 
'sysname') + '.' 
+ keylookups.keylookup.value('(Object/@Schema)[1]', 
'sysname') + '.' 
+ keylookups.keylookup.value('(Object/@Table)[1]', 'sysname')) AS[object_id] , 
keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') AS [database] , 
keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') AS [schema] , 
keylookups.keylookup.value('(Object/@Table)[1]', 'sysname') AS [table] , 
keylookups.keylookup.value('(Object/@Index)[1]', 'sysname') AS [index] , 
for $column in DefinedValues/DefinedValue/ColumnReference 
return string($column/@Column) 
).value('.', 'varchar(max)'), ' ', ', ') AS [columns] , 
plans.query_text , 
plans.execution_plan , 
plans.last_execution_time , 
plans.execution_count , 
plans.total_worker_time , 
FROM    @plans_in_cache AS [plans] 
CROSS APPLY execution_plan.nodes('//RelOp/IndexScan[@Lookup="1"]') AS keylookups( keylookup ) 

SELECT  lookups.[database] , 
lookups.[schema] , 
lookups.[table] , 
lookups.[index] , 
lookups.[columns] , 
index_stats.user_lookups , 
index_stats.last_user_lookup , 
lookups.execution_count , 
lookups.total_worker_time , 
lookups.total_physical_reads , 
lookups.last_execution_time , 
lookups.o_name AS [object_name], 
lookups.query_text , 
FROM    lookup_objects 
INNER JOIN sys.dm_db_index_usage_stats AS [index_stats] ON lookups.database_id =index_stats.database_id 
AND lookups.[object_id] =index_stats.[object_id] 
WHERE   index_stats.user_lookups > 0 
AND lookups.[database] NOT IN ('[master]','[model]','[msdb]','[tempdb]') 
ORDER BY lookups.execution_count DESC