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.
|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
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.