Sql-server – How to dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups

sql serversql-server-2008-r2

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?

Best Answer

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
Related Question