SQL Server: What's the Difference Between a 'Singleton Lookup' and a 'User Lookup'?

Microsoft describes the [user_lookup] counter in dm_db_index_usage_stats as the 'Number of bookmark lookups by user queries.' It describes the [singleton_lookup_count] in dm_db_index_operational_stats as the 'Cumulative count of single row retrievals from the index or heap.' This is not at all clarifying to me. Could someone provide a clearer definition/differentiation of what these two values are tracking?

The question stems from an index observation (800m row table) today where the current [user_lookup] value on the Cx was around 800 and the [singleton_lookup_count] was over 1 billion. They're obviously counting much different things and I need to understand what they are and whether a difference of that magnitude could be indicative of a problem.

These counters are not always easy to interpret for reasons I will explain shortly. They can be useful as a indicator there might be areas you need to analyse in more detail.

The question stems from an index observation (800m row table) today where the current [user_lookup] value on the Cx was around 800 and the [singleton_lookup_count] was over 1 billion.

The user_lookup value means a bookmark lookup operator (Key or RID Lookup) accessing the clustered index or heap appeared 800 times in executed plans.

The singleton_lookup_count value includes the number of rows looked up using the Key or RID Lookup operators. It also counts every guaranteed single-row access to the clustered index that is not a lookup. This occurs when the clustered index is unique, and one or more equality seek predicates are used.

A singleton lookup is a b-tree traversal guaranteed to find at most a single matching record. This is the case for each seek-able equality predicate applied to a unique index.

For example, select id from table where id in (1, 2, 3, 4) results in four singleton lookups if id is a unique index key and an index seek is used. Note, no bookmark lookup is used.

A Key or RID Lookup is nothing more than a unique index seek using an equality predicate, so they're counted the same.

A lookup is always to a unique 'index' even if the base table is a heap or the clustered index is not unique. In the case of a heap, the unique row locator is the heap RID. For a non-unique clustered index, the unique row locator is the clustered index key(s) plus any 'uniquifier' (present for actually duplicate keys only).
Index Usage Stats

The index usage stats DMV contains counts of index operations from the perspective of the Query Executor (QE). This is the SQL Server component responsible for executing the query plan.

name
description

user_seeks
Number of times an Index Seek operator appears in an executed plan.

user_scans
Number of times a Table Scan or Index Scan operator appears in an executed plan.

user_lookups
Number of times an RID or Key Lookup operator appears in an executed plan.

The way these counters are incremented is not entirely intuitive:

• An operator is counted once per plan execution.

• Generating an estimated plan does not affect the totals.

• An Index Seek that executes 10,000 times in a single plan execution still only adds 1 to the count of user seeks.

• An operator is counted even if it is not executed at all.

Index Operational Stats

The index operational stats DMV contains counts of index and table operations from the perspective of the Storage Engine (SE).

name
description

range_scan_count
Number of range scans (including unrestricted full scans) on a heap or index structure.

singleton_lookup_count
Number of singleton lookups in a heap or index structure.

This DMV counts each storage engine operation, making the counters perhaps more intuitive:

• 10,000 singleton lookups will add 10,000 to the singleton lookup count column

• A table scan that is executed 5 times will add 5 to the range scan count.

For examples showing when each counter is incremented, see my article Seeking Without Indexes.

StackExchange Database Administrators Q#315849, answer score: 10