NumOfUpdates on heap index table

Question

Hi all,
I'm curios about the figures on the HEAP tables...
but if this kind of table has no indexes... how could the script below return values on the column "NumOfUpdates"?
OK it's clear for the size 'cause are pages of data... but "NumOfUpdates" it confuses me... it's always related to the pages of data?

Thank Alen

DECLARE @Restart as datetime
SELECT @Restart = sqlserver_start_time FROM sys.dm_os_sys_info;
SELECT 
       DB_NAME([IXUS].[database_id]) AS [database name] , 
       OBJECT_NAME([IXUS].[object_id]) AS [Table name] , 
       IX.name AS Index_Name, 
       OBJECT_NAME([IXUS].[object_id]) + '_{N}_' + IX.name AS Excel_Cerca_vert, 
       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 - Ultima azione],
       @Restart as ServerRestart,
       DATEDIFF(day, IXUS.last_user_update, getdate()) as [GG da Ultima azione]
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

AND IX.is_primary_key = 0   /* This line excludes primary key constarint*/
AND IX.is_unique = 0        /* This line excludes unique key constarint*/
AND IXUS.user_updates <> 0    /* This line excludes indexes SQL Server hasn’t done any work with*/
AND IXUS.user_lookups = 0
AND IXUS.user_seeks = 0
AND IXUS.user_scans = 0
--AND IX.type_desc!= 'HEAP' /* No heap */

GROUP BY 
         DB_NAME([IXUS].[database_id]),
         OBJECT_NAME([IXUS].[object_id]),
         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
;

ss

Answer 1

I'm curios about the figures on the HEAP tables...but if this kind of table has no indexes.

You misinterpret "heap" table: A heap is a table without a clustered index (CI), but can have non-clustered index(es) (NCI), of course.

See Heaps (Tables without Clustered Indexes)

Answer 2

sys.dm_db_index_usage_stats also tracks data for an "index" with index_id = 0. That is, a heap.