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.

BackgroundA 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!

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.