How to gather Index usage information

Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the index_type is shown as HEAP, and NumOfSeeks as 0; whereas, in the same rows, it shows some non-zero numbers for NumOfScans and/or for NumOfLookups. Question: How do we interpret these rows?

REMARK: None of the tables have clustered index, hence all tables are HEAP. But in most rows of the output of the following query, the index name is showing (and not showing as NULL), and the index_type is also showing (as NONSLUSTER). So, the question is specific to output rows described above this remark.

Ref: sys.dm_db_index_usage_stats.

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 NumOfSeeks
       ,IXUS.user_scans AS NumOfScans
       ,IXUS.user_lookups AS NumOfLookups
       ,IXUS.user_updates AS NumOfUpdates
       ,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

Heaps are what the base table is called in SQL Server, when it doesn't have a clustered index on it. You are free to create nonclustered indexes on a heap.

Heaps don't have a name in sys.indexes because they are not indexes. The most common way heaps are described is as unordered pages. Indexes are logically ordered pages, by the columns in the key of the index.

You could join back to sys.tables and use ISNULL to replace the index name with the base table name for heaps.

While there are edge cases where you can seek without indexes, rows are identified in a heap via an internal identifier that is not easily exposed to users, or usable in a normally-written query as a seekable predicate.

StackExchange Database Administrators Q#326468, answer score: 6