LazyWriter

Well this is no story about a writer who is lazy. Ironically they actually are : ) But then today I thought to document something about SQL Server which I think about lot of times and then as usual forget to document.

Now to start with lazy writer is a process in sql server which helps in clearing the buffer cache. The buffer cache as the name suggests are the cache pages which sql sever uses to load the data from data pages for manipulation. Now Lazy Writer is the process which flushes out batches of dirty, aged buffers and makes them available to user processes. The lazy writer eliminates the need of perfomrning frequent checkpoint in order to create available buffers.

The process of clearing the buffer cache is not that easy. By that I mean it's calculative. Now each page in the buffer cache has a refernce counter and a bit to note whether it is a dirty page. (Now a dirty page is one which has modified data which has yet not been written back to disk.) Comming back to the calculative part the reference counter that I mentined associated to the page gets decremented each time a buffer scan happens. Now if the count is reduced to 0 then the page is written back to the data file page and the buffer page is emptied. This job is done by the lazy writer.

Basically LazyWriters ensure two things:
a) There are adequate resources in buffer pool for the sql server to use.
b) Monitor the usage of commited memory by the buffer pool and ajust it as necessary so that enough physical memory remains free to prevent windows from paging.

LazyWriter can adjust the number of buffers in the buffer pool if the dynamic memory management is enabled in SQL Server. SQL Server estimates the adequate number of buffer pools depending upon the system activity and the number of stalls.(Stall happens when the system has to wait for a buffer free page when a request for memory is raised)

Tushar