Ask anyone with performance tuning experience to list the most expensive operations in an execution plan and it's a safe bet you'll find key lookups near the top. The good news is they're usually easy to fix - Glenn Berry (Blog | Twitter) blogged about it recently and many others have written about the subject as well. For most people though, it's finding out when and where they're happening that's the tough part.

Background
A key lookup occurs when a nonclustered index is used to satisfy a query but doesn't include all the columns the query is asking for so they have to be retrieved from the corresponding clustered index (or heap if there's no clustered index) that the nonclustered index is based on. It's expensive because it requires fetching additional pages into the buffer which has a double whammy effect: if the pages aren't already in the buffer you have to wait for the disk subsystem to retrieve them and you're filling up the buffer with up to twice the number of data pages you'd need if you could just stick with using the nonclustered index to begin with.

Finding Key Lookups - There's a DMV For That
SQL Server keeps track of how many key lookups occur against every index in sys.dm_db_index_usage_stats (in the user_lookups column). Interesting, but to be useful we need to know what queries are causing the lookups. Enter the DMV sys.dm_exec_query_stats which keeps track of a wealth of information about how many times a query has been executed and the resources (CPU, disk, etc.) its used. Plug the sql_handle and plan_handle columns into the DMFs sys.dm_exec_sql_text and sys.dm_exec_query_plan, respectively, and we get the text and execution plan for the query. Because the execution plan is an XML document we can leverage SQL Server's native XML capabilities to find any key lookup operations that are occurring. Join them all together and - voila! - we can see every cached query that's got a key lookup, the additional columns being retrieved, and the execution plan, ordered by worst offender first - everything we need to know to work on eliminating the key lookups that are draining performance. Happy tuning!

NOTE: As the comments in the header suggest , exercise caution when running this against a production server…executing sys.dm_exec_query_plan can be resource intensive when your server contains a lot of cached plans. You have been warned!


/*********************************************************************************************
Find Key Lookups in Cached Plans v1.00 (2010-07-27)
(C) 2010, Kendal Van Dyke

Feedback: mailto:kendal.vandyke@gmail.com

License:
   This query is free to download and use for personal, educational, and internal
   corporate purposes, provided that this header is preserved. Redistribution or sale
   of this query, in whole or in part, is prohibited without the author's express
   written consent.
  
Note:
   Exercise caution when running this in production!

   The function sys.dm_exec_query_plan() is resource intensive and can put strain
   on a server when used to retrieve all cached query plans.

   Consider using TOP in the initial select statement (insert into @plans)
   to limit the impact of running this query or run during non-peak hours
*********************************************************************************************/
DECLARE @plans 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 @lookups 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] ,
                        [plan_handle]
             )
    INSERT  INTO @plans
            ( query_text ,
              o_name ,
              execution_plan ,
              last_execution_time ,
              execution_count ,
              total_worker_time ,
              total_physical_reads ,
              total_logical_reads
            )
            SELECT /*TOP 50*/
                    sql_text.[text] ,
                    CASE WHEN sql_text.objectid IS NOT NULL
                         THEN ISNULL(OBJECT_NAME(sql_text.objectid,
                                                 sql_text.[dbid]),
                                     'Unresolved')
                         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 ,
                    query_stats.total_logical_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 ;

;WITH XMLNAMESPACES (
   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] ,
           OBJECT_ID(keylookups.keylookup.value('(Object/@Database)[1]',
                                                '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