Finding Key Lookups In Cached Execution Plans

<div><p>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 <a href="http://msdn.microsoft.com/en-us/library/bb326635.aspx" target="_blank">key lookups</a> near the top. The good news is they're usually easy to fix - Glenn Berry (<a href="http://glennberrysqlperformance.spaces.live.com/blog/" target="_blank">Blog</a> | Twitter) <a href="http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!6962.entry" target="_blank">blogged about it recently</a> 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.</p> <p><strong>Background</strong> <br/>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.</p> <p><strong>Finding Key Lookups - There's a DMV For That <br/></strong>SQL Server keeps track of how many key lookups occur against every index in <a href="http://msdn.microsoft.com/en-us/library/ms188755.aspx" target="_blank">sys.dm_db_index_usage_stats</a> (in the user_lookups column). Interesting, but to be useful we need to know what queries are causing the lookups. Enter the DMV <a href="http://msdn.microsoft.com/en-us/library/ms189741.aspx" target="_blank">sys.dm_exec_query_stats</a> 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 <a href="http://msdn.microsoft.com/en-us/library/ms181929.aspx" target="_blank">sys.dm_exec_sql_text</a> and <a href="http://msdn.microsoft.com/en-us/library/ms189747.aspx" target="_blank">sys.dm_exec_query_plan</a>, 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!</p> <p>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!</p> <pre class="brush: sql">/********************************************************************************************* 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</pre></div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Performance Query Tuning Scripts