Category: ASE: Performance and Tuning: Caches and Buffer Pools

Unpin, unpinning

Unpin is a term taken from performance monitoring in ASE. Often, this term can be found in the output of the sp_sysmon stored procedure. It describes the removal of pages from the user log cache. Unpinning of data pages happens, if two transactions try to modify the same data page at the same time. In this case, the latter of the two transactions must remove the page already cached in the ULC of ULC der ersten gecacht ist the first transaction in order to pin it to its own cache. As a consequence, the whole ULC of the first transaction will be emptied (flushed).

Example for Unpinning

A transaction modifies a data row in a table. To achive this, ASE pins the page on which the row is stored to the ULC of the executing process. When subsequent transaction wants to modify another data row on the same page, it notices that this page is already pinned to the ULC of the first transaction. It must remove the page from the ULC of the first transaction in order to modify it. For this the second transaction will flush the first transaction's ULC and thereby empty it.

There are two possible ways to avoid unpinning. One solution would be to modify the application logic to always address different data pages for modification. Or, as a second approach, distribute data to a larger number of data pages to avoid conflict, using the stored procedure sp_chgattribute with the max_rows_per_page option.