Thanks Gail
Regarding the free buffer list. I understand that the Lazy Writer is responsible for maintaining a decent amount of free buffers to serve incoming requests. I found the following statement:
The work of scanning the buffer pool, writing dirty pages, and populating the free buffer list
is primarily performed by the individual workers after they have scheduled an asynchronous
read and before the read is completed. The worker gets the address of a section of the buffer
pool containing 64 buffers from a central data structure in the SQL Server Database Engine.
Once the read has been initiated, the worker checks to see whether the free list is too small.
(Note that this process has consumed one or more pages of the list for its own read.) If so,
the worker searches for buffers to free up, examining all 64 buffers, regardless of how many
it actually finds to free up in that group of 64. If a write must be performed for a dirty buffer
in the scanned section, the write is also scheduled.
Is this a readahead?
I'm asking because I setup a test scenario, the max memory was 3GB and I did a select * on my largest tables which is way more that 3GB. I didn't notice an increase in lazy writes, my PLE was however extremely low. Is this because of the quote above?
Thanks