Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm probably not understanding something but here is the question.

I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 thousand) in the user_lookups column but these also have a zero in both the user_seeks and user_scans columns.

My understanding (from books online) is that the user_lookups column counts bookmark lookups. As I understand it that is when the database can't find all the columns it needs in an index and has to visit the main table or clustered index for the remaining columns. However why would SQL server have done this if it has never done an seek or scan on the index?

share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

The user_lookups are lookups in to the index. The original scan or seek will have been on another non covering index.

For example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y INT CONSTRAINT UQ UNIQUE NONCLUSTERED,
Z CHAR(8000) NULL
)

INSERT INTO T(Y)
SELECT DISTINCT number 
FROM master..spt_values

GO

DECLARE @Z INT
SELECT @Z = Z
FROM T 
WHERE Y = 1

GO 1000

Gives the following plan

Plan

SELECT index_id, user_seeks,user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
WHERE object_id = object_id('T')
ORDER BY index_id

Shows 1,000 seeks on the NCI and 1,000 lookups on the CI.

index_id    user_seeks           user_scans           user_lookups
----------- -------------------- -------------------- --------------------
1           0                    0                    1000
2           1000                 0                    0

BTW: Just in case you were not aware the user_lookups shown here refers to the number of times a plan was executed containing the lookup operator not the number of lookups that actually occurred. e.g. Both of the following increment the counter by 1 despite performing 0 and 2,161 lookups in reality

SELECT  Z
FROM T 
WHERE Y = -999

SELECT Z
FROM T WITH (FORCESEEK)
WHERE Y >= 0
share|improve this answer
2  
Nice one, Martin. –  Marian Feb 23 '12 at 13:39
    
So how do you find out which index(s) it is that need the extra columns in, to prevent the lookup? –  Martin Brown Feb 25 '12 at 12:28
    
@MartinBrown - By looking at the execution plans for the queries that reference that table. You could probably write something that searches the plan cache and looks in the plan XML for key lookup operators against that clustered index or bookmark lookups for a heap. –  Martin Smith Feb 25 '12 at 12:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.