Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding Key Lookups In Cached Execution Plans

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 [table] ,
          
keylookups.keylookup.value('(Object/@Index)[1]', 'sysname') AS [index] ,
          
REPLACE(keylookups.keylookup.query('
for $column in DefinedValues/DefinedValue/ColumnReference
return string($column/@Column)
'
).value('.', 'varchar(max)'), ' ', ', ') AS [columns] ,
          
plans.query_text ,
          
plans.o_name,
          
plans.execution_plan ,
          
plans.last_execution_time ,
          
plans.execution_count ,
          
plans.total_worker_time ,
          
plans.total_physical_reads,
          
plans.total_logical_reads
  
FROM    @plans 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.total_logical_reads,
       
lookups.last_execution_time ,
      
lookups.o_name AS [object_name],
       
lookups.query_text ,
       
lookups.execution_plan
FROM    lookups
       
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
--ORDER BY index_stats.user_lookups DESC
--ORDER BY lookups.total_logical_reads DESC

Comments

Posted by Jerrry Brenner on 30 July 2010

This is definitely helpful.  The question that I sometimes want to answer is, "Which queries are making the hot objects hot?"  A different variation would be, "Which queries are causing the most key lookups on the hot objects?"  (The questions that I often want to answer include, "How much execution time or i/o for a given query is spent at a particular RelOp node in the plan?" and "How many rows are being returned by each RelOp node in the plan?") While the DMVs enable us to identify the hot queries and hot objects by different criteria and how the hot queries are accessing those objects, there's not enough information to identify the queries that are making the hot objects hot.  The DMVs can tell us that the top query by logical (or physical) reads is doing a key lookup on the table with the most key lookups, but they don't tell us the percentage of the key lookups on that table are due to that query.  That would be extremely useful.  

In theory, it seems like it would be possible to enhance your query to include whether or not rows are being filtered out on the table from the key lookup.  This would then signal whether to add the columns as included or key columns.  (Just because rows are being filtered out on one of these columns, it doesn't mean that adding it as a key column, as opposed to as an included column, would be beneficial.  That depends on how many rows would be filtered out, etc.)

These questions can be answered when running against Oracle with STATISTICS_LEVEL = ALL.  There's some additional overhead with that option and people don't generally run with it in a production system, but we run with it in all of our performance tests and find that the benefits far outweigh any costs.

Posted by Steve Jones on 30 July 2010

Good points from Jerry, and some I hear asked. Too often I think blogs and articles like this lead people to believe that they should eliminate all key lookups. We can't have all queries covered in non-clustered indexes, nor do we want this.

What we want is a judicious use of tools to eliminate some key lookups. If we have queries that are run once a day to check some field no other query looks for and isn't in the NCI, we don't want to necessarily eliminate that key lookup.

A good future  topic might be which lookups you eliminate.

Posted by Kendal Van Dyke on 30 July 2010

Jerry, good points. Call this a v1 query - better than not knowing at all, but certainly room to improve. I've already thought about enhancing this to be smarter about finding the lookups that are x% of the total query cost so that you can get a better idea of which ones are really the most expensive. The information's there in the execution plans - it's just a matter of writing the write XPath queries to get it.

Steve, it wasn't my intent to suggest that you eliminate all key lookups...just trying to provide another tool to help you figure out what to look into further. Good suggestion for a followup and I'll work on getting something out soon.

Posted by Jerrry Brenner on 30 July 2010

The query plan contains the estimated cost.  While it's useful to expose that, it's the actual cost that's key (and not available).  We rely on that information when tuning on Oracle and wish we had it on SQL Server.  Google "tuning by cardinality feedback".  

Posted by ianstirk on 2 August 2010

Hi,

I have written (and documented) a routine that allows you to search for any item in a cached plan (for example lookups, missing indexes, missing stats, a comment etc).

Whilst the routine may run slower than the routine given here, it is more generic and hence very flexible, hopefully requiring less maintenance and understanding.

The routine can be found here: www.sqlservercentral.com/.../66729

You might also be interested in my forthcoming book about DMVs that can be found here: www.mannnig.com/stirk

Thanks

Ian

Posted by ianstirk on 2 August 2010

Oops... URL needed correcting: www.manning.com/stirk

Hi,

I have written (and documented) a routine that allows you to search for any item in a cached plan (for example lookups, missing indexes, missing stats, a comment etc).

Whilst the routine may run slower than the routine given here, it is more generic and hence very flexible, hopefully requiring less maintenance and understanding.

The routine can be found here: www.sqlservercentral.com/.../66729

You might also be interested in my forthcoming book about DMVs that can be found here: www.manning.com/stirk

Thanks

Ian

Leave a Comment

Please register or log in to leave a comment.