Table with index lookup without seek

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
       , 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
GROUP BY OBJECT_NAME(IX.OBJECT_ID) , ,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

>Solution :

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.

Leave a Reply