For inserts to heap tables, the insert takes place:
On the last page of a table that uses allpages locking
On a page that was recently used for a successful insert, on a table that uses data-only-locking
If an insert is the first row on a new page for the table, a clean data buffer is allocated to store the data page, as shown in Figure 8-5. This page starts to move down the MRU/LRU chain in the data cache as other processes read pages into memory.
If a second insert to the page takes place while the page is still in memory, the page is located in cache, and moves back to the top of the MRU/LRU chain.
Figure 8-5: Inserts to a heap page in the data cache
The changed data page remains in cache until it reaches the LRU end of the chain of pages. The page may be changed or referenced many times while it is in the cache, but it is written to disk only when one of the following takes place:
The page moves past the wash marker.
A checkpoint or the housekeeper wash task writes it to disk.
“Data cache” explains more about these processes.
