Avoiding hot spots

Hot spots occur when all updates take place on a certain page, as in an allpages-locked heap table, where all inserts happen on the last page of the page chain.

For example, an unindexed history table that is updated by everyone always has lock contention on the last page. This sample output from sp_sysmon shows that 11.9% of the inserts on a heap table need to wait for the lock:

Last Page Locks on Heaps
Granted                     3.0     0.4     185      88.1 %
Waited                      0.4     0.0      25      11.9 %

Possible solutions are: