0

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
nam
  • 21,967
  • 37
  • 158
  • 332
  • Please show the result for the other two indexes also, presumably they were used and then the heap was looked up. *I have a HEAP table that has two nonclustered indexes* is mistake #1: if you need indexes why use a heap? – Charlieface May 11 '23 at 00:20
  • @Charlieface You probably answered my question. Per your request, I've added the row for a `noncustered` index. Also, it's an `OLAP` project that has huge tables - and hence uses no clustered index. – nam May 11 '23 at 00:34
  • 1
    Nope, should still have a clustered index. For fast reloading you can truncate and drop it, then recreate it. It makes no sense to use a NCI without a CI. – Charlieface May 11 '23 at 00:37
  • As they are both 11.6GB clearly the NCI is just a complete copy of the table so basically the same as a CI would be but with an additional useless copy of the data. the extract above is just one heap and one NCI by the way not two indexes though – Martin Smith May 11 '23 at 03:33
  • @MartinSmith Agree with your comment. Question: The HEAP has size 11.6GB that is `77.33%` of the table size 15GB. Would you know why then they are calling it a HEAP in query result shown in my post? – nam May 11 '23 at 14:58

1 Answers1

2

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I agree with your argument on clustered index. For some reasons, management has all the tables as HEAP. One reason seems to be that not all tables have ETL process to be `KILL and FILL`. There are many tables (including the one used in the post above) that use `APPEND` instead. – nam May 11 '23 at 00:45
  • Still you should use a clustered index. Heaps are only useful for logging or work tables, or for fast loading before adding a CI. – Charlieface May 11 '23 at 09:29