EXEC sp_BlitzCache @SortOrder = 'cpu'
Long Running Queries
- Check for missing index – Check “Execution Plan” for missing index.
Expensive Key Lookups
Check “Execution Plan” for Key Lookup.
A key lookup occurs when SQL uses a nonclustered index to satisfy all or some of a query’s predicates, but it doesn’t contain all the information needed to cover the query. This can happen in two ways: either the columns in your select list aren’t part of the index definition, or an additional predicate isn’t.
Key Lookups effectively join the nonclustered index back to the clustered index, on the clustered index key columns to retrieve the necessary information.
This is what a Key Lookup operator looks like. Key Lookups are always tied to Nested Loops Joins, executing once for each row that needs to be retrieved. This can be especially painful in instances of parameter sniffing, when a clustered index scan would have been more appropriate.