Hot spots occur when all updates take place on a certain page, as in an allpages-locked heap table, where all insertions 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 %
To avoid this:
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 using the round-robin strategy. Partitioning a heap table creates multiple page chains in the table, and, therefore, multiple last pages for insertions.
Concurrent inserts to the table are less likely to block one another, since multiple last pages are available. Partitioning improves concurrency for heap tables without creating separate tables for different groups of users.
See “Improving insert performance with partitions” in Performance and Tuning Series: Physical Database Tuning for information about partitioning tables.
Create a clustered index to distribute updates across the data pages in the table.
Like partitioning, this creates multiple insertion points for the table. However, it also introduces overhead for maintaining the physical order of the table’s rows.
