I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated.
Using a script of Jonathan Kehayas I go through the list of query plans that used those lookups and compare this to the information sql server gives me regarding missing indexes:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.query('.'),
i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,
i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,
i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,
i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,
i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,
i.query('.'),
STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
FROM i.nodes('./OutputList/ColumnReference') AS t(cg)
FOR XML PATH('')),1,2,'') AS output_columns,
STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg)
FOR XML PATH('')),1,2,'') AS seek_columns,
i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate,
cp.usecounts,
query_plan
FROM ( SELECT plan_handle, query_plan
FROM ( SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp
ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as s(i)
OPTION(RECOMPILE, MAXDOP 1);
But very often, after I extract the query and re-execute it with some valid scalar parameters, it shows that the actual query plan does not (anymore) perform a key lookup. So maybe this is some caching issue and/or it is because I changed the query by passing different parameters... I don't know.
However, if the indexes allow the query to do a nice seek operation right now, where does this plan come from? Are those plans kind of old? What are possible reasons?
Is it maybe time to reset this cache to be sure there is no old stuff inside?