If
a non clustered index is created on expected WHERE clause and JOIN
columns of a query but still to satisfy the query and to get values for
column which are not included in non-clustered index, optimizer refers
to data pages. This trip to data pages to satisfy a query, although you
have a valid non-clustered index, is called bookmark lookup.
I
remember my early days of DBA career, when bookmark lookups were a big
performance problem for me. As I was really worry that performance is
not up to the mark although I have proper non-clustered indexes.
Let’s try to understand this phenomenon through an example.
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX [IX_StandardCost]
ON [Production].[Product] ( [StandardCost] ASC )
ON [PRIMARY]
GO
SELECT ProductID, Name, ProductNumber, StandardCost
FROM Production.Product
WHERE StandardCost = 1265.6195
Table 'Product'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0
In our example, bookmark lookup occurred because two columns in select “ Name, ProductNumber” are not included in non-clustered index.What about ProductID , we will discuss it later in this post.
Problem
can be resolved if non-clustered can satisfy all required columns. We
never want to use these extra columns in index key to avoid wide key
ambiguities. Covering index is best solution. For this we will modify our non-clustered index IX_StandardCos, by using include keyword as follow.
CREATE NONCLUSTERED INDEX [IX_StandardCost]
ON [Production].[Product] ( [StandardCost] ASC )
INCLUDE ( ProductID, Name, ProductNumber )
WITH (DROP_EXISTING = ON)
ON [PRIMARY]
GO
SELECT ProductID, Name, ProductNumber, StandardCost
FROM Production.Product
WHERE StandardCost = 1265.6195
Table 'Product'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0
Covering
index solved our problem and we were successful to optimize our query,
but in real word identifying columns which should we include in COVERING
INDEX is not so simple. Optimizer may need columns used in any part of
query like SELECT clause, WHERE clause, JOINs, FUNCTIONS, GROUP BY etc
There
is a solution for every problem. I like to use following methods to
identify columns that must be included in non-clustered index.
Method-1:
Move your mouse pointer to “Key Lookup” and note down columns mentioned in “Output List” section.
Method-2
Click on “Key Lookup”
, and press F4 to open properties window. Move to “Ouptpu List” and
click on browse button to open popup window. From here you can easily
copy name of columns to add in INCOLUDE list of non-clustered index.
No comments:
Post a Comment