TSQL : HEAP table usage script

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,'!')

TSQL : HEAPS and Forwarded Records

A little bemused that I'm still finding this in application code in the 21st Century, but there you go. A live app experiencing performance problems due to a significant number of heaps, each frequently used.

SELECT OBJECT_NAME(stats.object_id) as [Table],
idx.name as [Index],
stats.index_type_desc,
stats.page_count,
stats.avg_fragmentation_in_percent,
stats.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS stats
INNER JOIN sys.indexes AS idx (NOLOCK)
ON stats.OBJECT_ID = idx.OBJECT_ID
AND stats.index_id = idx.index_id
WHERE forwarded_record_count > 0
ORDER BY forwarded_record_count desc

See also : HEAP table usage script

TSQL : Clustered Indexes, Identities & Primary Keys

I'm Currently looking at a large database where there are

• HEAPS (tables without a clustered index)
• IDENTITY columns that could serve as Primary Keys (but aren't)
• PRIMARY KEYS that have been defined as non-clustered (someone has scripted them out and ignored the default clustered status of a PK).

Anyway, this is coming in useful ...

SELECT   SCHEMA_NAME(schema_id) AS SchemaName
  ,name AS TableName
  ,OBJECTPROPERTY(object_id,'TableHasClustIndex') HasClusteredIndex
  ,OBJECTPROPERTY(object_id,'TableHasIdentity') HasIdentity
  ,OBJECTPROPERTY(object_id,'TableHasprimarykey') HasPrimaryKey
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2