RSS

Bookmark lookup , Key lookup , RID lookup in SQL Server

17 Jun

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.

1

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 .

2

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 .

3

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 :

4

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

5

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

Drop All Index

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 )

6

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 ?

 
Leave a comment

Posted by on June 17, 2013 in Index, Performance MSSQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s