“APL Heap Tables” reports the number of row inserts that took place on allpages-locked heap tables—all tables that do not have a clustered index. This includes:
Partitioned heap tables
Unpartitioned heap tables
Slow bulk copy inserts into heap tables
select into commands
Inserts into worktables
The “% of total” column shows the percentage of row inserts into heap tables as a percentage of the total number of inserts.
If there are a large number of inserts to heap tables, determine if these inserts are generating contention.
Check the sp_sysmon report for data on last page locks on heaps in “Lock detail”. If there appears to be a contention problem, Adaptive Server Monitor can help you figure out which tables are involved.
In many cases, creating a clustered index that randomizes insert activity solves the performance problems for heaps. In other cases, you might need to establish partitions on an unpartitioned table or increase the number of partitions on a partitioned table.
For more information, see Chapter 12, “How Indexes Work” and “Improving insert performance with partitions” on page 101 in Performance and Tuning: Basics.
