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:
Change the lock scheme to datapages or datarows locking.
Since these locking schemes do not have chained data pages, they can allocate additional pages when blocking occurs for inserts.
Partition the table. Partitioning a heap table creates multiple page chains in the table, and, therefore, multiple last pages for inserts.
Concurrent inserts to the table are less likely to block one another, since multiple last pages are available. Partitioning provides a way to improve concurrency for heap tables without creating separate tables for different groups of users.
See “Improving insert performance with partitions” on page 101 in Performance and Tuning: General Information for information about partitioning tables.
Create a clustered index to distribute the updates across the data pages in the table.
Like partitioning, this solution creates multiple insertion points for the table. However, it also introduces overhead for maintaining the physical order of the table’s rows.
