USE master
GO

EXEC sp_BlitzCache @SortOrder = 'cpu'

Issues:

Long Running Queries

https://www.brentozar.com/blitzcache/long-running-queries/

  • Check for missing index – Check “Execution Plan” for missing index.

Expensive Key Lookups

https://www.brentozar.com/blitzcache/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.

Last modified: November 1, 2021

Author

Comments

Write a Reply or Comment