How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?

Question

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?

Answer 1

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

• Martin Brown: So how do you find out which index(s) it is that need the extra columns in, to prevent the lookup?
• Martin Smith: @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.