The Lazy Writer in SQL server is a background thread which manages allocation and deallocation of memory pages in the memory.
It is key component of memory management subsystem of SQL Server.
It continuously runs & searches the buffer pool & try to find those pages which are least recently used (LRU).
The main & important role of Lazy Writer is to provide sufficient memory to the database to perform its operations efficiently.
An algorithm named as Least Recently Used (LRU) is used by Lazy Writer to decide which pages to evict from buffer pool. It tracks the timestamp of last access of each page. The pages with the oldest timestamp are considered as least recently used and are ready for eviction.
The Lazy Writer works on below two thresholds:
1. Low Memory Threshold - Occurs when the free memory falls below a certain threshold. Lazy Writer start evicting pages from the buffer pool to make more free space in the memory.
SQL Server instance set the Low Memory Threshold during startup & is totally based on the available physical memory on the server.
2. High Memory Threshold - Occurs when the free memory rises above a certain threshold. Lazy Writer stops evicting pages from the buffer pool.
Lazy Writer calculates High Memory Threshold which is based on size of buffer pool and the memory required by the processes to run & complete the process on the server.
No comments:
Post a Comment