In MS SQL Server, does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free? Or free marked clean pages continue to stay in the buffer pool until another page is needed to retrieve from the disk and the latter overwrites the free marked clean page?
SQL Server – Does Lazy Writer Use Resources for Flushing Buffer Pool Pages?
buffer-poolsql server
Related Question
- SQL Server – Difference Between VAS and Buffer Pool
- Mysql – Does innodb_data_reads include reads from the buffer pool
- Sql-server – Page Life Expectancy (PLE), where to start
- Mysql – Would the MySQL database benefit from increasing the InnoDB buffer pool size
- SQL Server Buffer Pool – When Do Transaction Log Changes Get Applied to Database Data Pages?
- MySQL – How Does MySQL Evict Pages from the Buffer Pool?
- Innodb – Partitioning and the InnoDB Buffer Pool
Best Answer
Yes it does, this is primary responsibility of Lazy writer. Its job is to make sure there is some free buffer so that new pages can be brought into memory when required.
Lazy writer would periodically scan buffer pool, look for pages which are least recently used, or you can say "cold pages" and flush them to disk. In case there is sufficient memory available Lazy writer would not do anything. In case of memory being required to bring more page into buffer pool Lazy writer would flush old pages, create space and bring new pages. It does not marks pages as clean