When you insert data into an allpages-locked heap table without a clustered index, the data row is always added to the last page of the table. If there is no clustered index on a table, and the table is not partitioned, the syspartitions.root entry for the heap table stores a pointer to the last page of the heap to indicate the page where the data should be inserted.
If the last page is full, a new page is allocated in the current extent and linked onto the chain. If the extent is full, Adaptive Server looks for empty pages on other extents being used by the table. If no pages are available, a new extent is allocated to the table.
One of the severe performance limits on heap tables that use allpages locking is that the page must be locked when the row is added, and the lock is held until the transaction completes. If many users are trying to insert into an allpages-locked heap table simultaneously, each insert must wait for the preceding transaction to complete.
This problem of last-page conflicts on heap tables is true for:
Single-row inserts
Multiple row inserts using select into or insert...select, or several insert statements in a batch
Bulk copying into the table
To address last-page conflicts on heap tables, try:
Switching to datapages or datarows locking
Creating a clustered index that directs the inserts to different pages
Partitioning the table, which creates multiple insert points for the table, giving you multiple “last pages” in an allpages-locked table
For all transactions where there may be lock conflicts, you can also:
Keep transactions short
Avoid network activity and user interaction whenever possible, once a transaction acquires locks
