Performance Tuning- RIDLookup VS KeyLookup (Day 2)
Speaker: Rajasekhar Reddy Bolla, +91 9966246368 (whatsapp)
SQL Server execution plans provide insight into how a query is executed by the SQL Server engine. Here are some common operators in execution plans that may signal inefficiencies, along with strategies to avoid them, and practical examples for each:
3. Key Lookup (RID Lookup)
• What it is: Happens when a non-clustered index is used, but additional columns are retrieved, causing extra lookups in the clustered index or heap.
• Why it’s bad: Adds extra overhead due to multiple lookups.
Example of Key Lookup:
-- Problematic Query
SELECT SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11000;
-- Solution: Create a Covering Index
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader (CustomerID)
INCLUDE (SalesOrderID, OrderDate, TotalDue);
-- Optimized Query
SELECT SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11000;
________________________________________