This script reports usage of HEAP tables over a 15 minute period.
It reports reads, writes and row counts for the tables.
I wrote it as I found a system exhibiting high values for Performance Counter 'forwarded records/sec'.
See Also :
TSQL : HEAPS and forwarded records
IF OBJECT_ID('tempdb..#Snap1') IS NOT NULL DROP TABLE #Snap1
IF OBJECT_ID('tempdb..#Snap2') IS NOT NULL DROP TABLE #Snap2
IF OBJECT_ID('tempdb..#rowcounts') IS NOT NULL DROP TABLE #rowcounts
SELECT
s.object_id
, OBJECT_SCHEMA_NAME(s.object_id) AS schemaname
, OBJECT_NAME(s.object_id) AS tablename
, i.name AS indexname
, SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes = SUM(user_updates)
INTO #snap1
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS
GROUP BY s.object_id,i.name
WAITFOR DELAY '00:15:00'
SELECT
s.object_id
, OBJECT_SCHEMA_NAME(s.object_id) AS schemaname
, OBJECT_NAME(s.object_id) AS tablename
, i.name AS indexname
, SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes = SUM(user_updates)
INTO #snap2
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS
GROUP BY s.object_id,i.name
SELECT sys_schemas.name AS schemaName
,sys_objects.name AS tableName
,sys_indexes.name AS indexName
,SUM(partition_stats.used_page_count) * 8 AS indexsizekb
,MAX(partitions.rows) AS tablerows
INTO #rowcounts
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
ON partition_stats.[object_id] = sys_indexes.[object_id]
AND partition_stats.index_id = sys_indexes.index_id
INNER JOIN sys.objects sys_objects
ON sys_objects.[object_id] = partition_stats.[object_id]
INNER JOIN sys.schemas sys_schemas
ON sys_objects.[schema_id] = sys_schemas.[schema_id]
AND sys_schemas.name <> 'SYS'
INNER JOIN sys.partitions partitions
ON partitions.object_id = partition_stats.object_id
WHERE OBJECTPROPERTY(sys_objects.object_id,'TableHasClustIndex') = 0
GROUP BY sys_schemas.name
,sys_objects.name
,sys_indexes.name
ORDER BY sys_schemas.name
,sys_objects.name
,sys_indexes.name
SELECT i.schemaname
,i.tablename
,i.indexname
,(j.user_seeks - i.user_seeks) AS seeks_period
,(i.user_scans - i.user_scans) AS scans_period
,(j.user_lookups - i.user_lookups) AS lookups_period
,(j.reads - i.reads) AS reads_period
,(j.writes - i.writes) AS writes_period
,r.tablerows
,r.indexsizekb
FROM #Snap1 i
INNER JOIN #Snap2 j ON i.tablename = j.tablename
INNER JOIN #rowcounts r ON r.tableName = i.tablename AND r.indexName = i.indexname
AND COALESCE(i.indexname,'!') = COALESCE(j.indexname,'!')
