Introduction :
Today i will Show :
What is the Meaning of Bookmark lookup , Key lookup , RID lookup in SQL Server ?
when SQL Server Retrieve Bookmark lookup , Key lookup , RID lookup ?
How can we Remove the Bookmark lookup , Key lookup , RID lookup ?
Bookmark lookup , Key lookup , RID lookup Meaning and when SQL Server Retrieve it ?
- bookmark lookup or RID lookup showing in the Execuatuon plan when you select Columns not included on your index
- if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup.
- You Must remove the lookup from the Execution plan to improve your Performance .
now if we nee to return Small Data by Query SQl Server optimizer will go to use Non Clustered index to the Columns in the Where Condition so if this Columns in the Where Condition not in the Non-Clustered index SQL Server will go to Select from data Pages .Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data..
How can I remove bookmark lookup , RID lookup ?
by two Method
1- Non-Clustered Covered Index .
2- included Column in Non-Clustered Index .
I will Show this two methods later but now i will go to Demo to Show all thing about bookmark Lookup , RID lookup :
Demo :
i will work on SQL Server 2012 and Database Adventurework2012 you can download it from the internet if you need to know how to Attach it on your SQL Server Mangment Check this post How to Install DB Without LDF File
Bookmark Lookup :
in the first i will build sample table Name MySalesOrderDetail without any index
USE AdventureWorks2012
GO
SELECT *
INTO MySalesOrderDetail
FROM [Sales].[SalesOrderDetail]
GO
Select Some Data from the Table :
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
if you Check the Execuation plan you will find the Result is TABLE SCAN because the table not have any index.
Create Clustered Index on Column Name SalesOrderID:
CREATE CLUSTERED INDEX [ix_MySalesOrderDetail_SalesOrderID] ON [dbo].MySalesOrderDetail
(
SalesOrderID ASC
) ON [PRIMARY]
GO
Select the Data Again :
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
Check the Execution Plan you will ind the Result is changed form TABLE SCAN TO INDEX SCAN .
Create NON-Clustered Index on Columns ([ProductID],[OrderQty],[SpecialOfferID])
CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID]
ON MySalesOrderDetail
([ProductID],[OrderQty],[SpecialOfferID])
GO
Select the Data :
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
Check now the Execuation plan Result you will Find Key Lookup (bookMark LookUp) that’s Meaning we have Columns included in the Select Query but not included in the Non-Clustered Index [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID] this Columns is (SalesOrderDetailID)
also you will find the Execution plan give you Alert (Missing Index ) this Alert will remove this Key Lookup i will show it but step by step .
to know what Columns make the problem and Key lookup Show on it stop your mouse on the Name of (Key lookup Clustered)
the Result :
Remove Key Lookup
Right Click on Missing Index >> Missing Index Detail >> Take the Result and Rename the name of the index and Run this Query
The Query is New Non-Clustered index Include the Columns on the previous Non-Clustered Index Plus the SalesOrderDetailID
Create the Missing index
CREATE NONCLUSTERED INDEX [Ix_MySalesOrderDetail_includeColumns]
ON [dbo].[MySalesOrderDetail] ([ProductID])
INCLUDE ([SalesOrderID],[SalesOrderDetailID],[OrderQty],[SpecialOfferID])
GO
Retrieve the Data
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
Check the Execution plan Now the key lookup is Removed and the result is the best Result INDEX SEEK
now you can Remove the First Non-Clustered Index and if you Select the Data Again the Result of the Execuation plan will be the same INDEX SEEK
Clean Up :
DROP INDEX [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID]
ON [dbo].[MySalesOrderDetail]
GO
Now we need to Drop all index on the table to Show the Next point is RID Lookup . to Drop All index in Table Check This Script
RID LOOKUP :
go to Retrieve your Data
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
Check the Execution plan you will find it TABLE SCAN. So we will Create NON-Clustered Index to Solve this
Create Non-Clustered index on Table name (MySalesOrderDetail)
CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID]
ON MySalesOrderDetail
([ProductID],[OrderQty],[SpecialOfferID])
GO
Now Retrieve your Data Again :
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
Check the Execution plan wooooow you found new result RID LOOKUP , MISSING INDEX
the RID LOOKUP Created on Columns Name ( SalesOrderID , SalesOrderDetailID )
Remove the RID LOOKUP :
The Same Steps we do it before in Remove KEY LOOKUP.
So i will Create the New Index of the Missing Index to Remove the RID LOOKUP
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [ix_MySalesOrderDetail_IncludeedColumn]
ON [dbo].[MySalesOrderDetail] ([ProductID])
INCLUDE ([SalesOrderID],[SalesOrderDetailID],[OrderQty],[SpecialOfferID])
GO
Retrive the Data Again
SELECT SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty,
SpecialOfferID
FROM MySalesOrderDetail
WHERE ProductID = 789
GO
Wooooooooooooow the RID LOOKUP removed .
now in the Final we know
What is Lookup ?
What is the Type of LookUP (RID , KEY) LOOKUP ?
When the RID LOOKUP showed in the Execution plan and When the KEY LOOKUP Showed in the Execution plan ?
How we Can Remove the RID LOOKUP and KEY LOOKUP to improve the Query Performance ?
DB Cloud TECH 