What is Lazy Writer in SQL Server? What is the use of Lazy Writer in SQL Server?

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.

Lazy Writer uses 'asynchronous I/O' technique to perform writes to disk.

Asynchronous I/O technique provides feature to write pages to disk as background process without affecting performance of other operations.

Lazy Writer removes those pages which are currently not in use by any active queries or transactions.

Lazy Writer also performs below tasks like :

a. Flush out modified pages from memory to disk

b. Recover memory from terminated abnormally processes.

SQL Server provides few configuration settings for Lazy Writer which control the behaviour and are as follows:-

a) Min server memory: Minimum amount of memory which will be available for SQL Server use. If the memory usage is below this threshold, Lazy Writer will not evict pages from memory.

b) Max server memory: Maximum amount of memory which will be available SQL Server use. If the memory usage exceeds this threshold, Lazy Writer will remove pages from memory. 

c) Recovery interval: Lazy Writer frequency to write modified pages from memory to disk for data consistency.

d) Page life expectancy: The Page Life Expectancy (PLE) is amount of time (In seconds) which tells the lazy writer to remain the data page in the buffer cache or buffer memory.