How to gather Index usage information

Question

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

Answer 1

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.

Answer 2

None of the tables have clustered index, hence all tables are HEAP.

Question: How do we interpret these rows?

Without a clustered index the table itself is stored in a heap data structure, as you've already noted. Those rows from sys.dm_db_index_usage_stats represent the metrics on how many times the heap was used in a query plan, whether it was fully scanned, etc.

A heap is an unordered set of data, by definition, so it is not possible to efficiently seek on it like a clustered index would be able to. That's why the number of seeks is 0 but the number of scans can be more than 0 (which again, effectively means the entire heap was scanned). If your table has a clustered index on it, then instead of a heap, the data would be stored in a B-Tree. Then you would see a row for that clustered index (with its name) instead of the row for the heap.

The inability of being able to be seeked on is typically why heaps are less performant than clustered indexed tables, for most queries that don't involve the majority of the data at one time.