Table with index lookup without seek

Question

From the time SQL Server restarted, following code returns counts of different types of index operations and the time each type of operation was last performed. Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter. But when I run the following query, in some cases it returns count of index seeks to be 0 and the count of lookups to be greater than 0.

For example, I have a HEAP table that has two nonclustered indexes. And on that table, the following code is showing the result as follows. Question: If there was no index seek then how Lookup was used? Please since it's a HEAP table, the lookups are RID Lookup. Unfortunately, sys.dm_db_index_usage_stats does not gives the details about the queries that caused this statistics.

Table	Table_Size	IndexName	IndexType	IndexSize	SeeksCount	ScansCount	LookupsCount	UpdateCount	LastSeek	LastScan	LastLookup	LastUpdate
Table1	15GB	NULL	HEAP	11.6GB	0	6	23	0	NULL	5/8/2023 4:42:50 PM	5/6/2023 7:51:20 PM	NULL
Table1	15GB	idx_appNumber	NONCLUSTERED	11.6GB	23	0	0	0	5/6/2023 7:51:20 PM	NULL	NULL	NULL

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
       ,IX.name AS Index_Name
       ,IX.type_desc Index_Type
       ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
       ,IXUS.user_seeks AS SeeksCount
       ,IXUS.user_scans AS ScansCount
       ,IXUS.user_lookups AS LookupsCount
       ,IXUS.user_updates AS UpdatesCount
       ,IXUS.last_user_seek AS LastSeek
       ,IXUS.last_user_scan AS LastScan
       ,IXUS.last_user_lookup AS LastLookup
       ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

Answer 1

You can clearly see that the idx_appNumber index has had 23 seeks, this has happened at the same time as the RID lookups on the heap. This would be a pretty normal Index Seek -> RID Lookup pair in a query plan.

I'd advise to change one of the non-clustered indexes to clustered, it makes no sense to have a heap with an NCI. This would remove the lookups completely. For fast loading, you can truncate the table and drop the index, then recreate the index afterwards.