October 29, 2013 at 5:07 am
Hi all
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 the above 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. As I said, I didn't notice an increase in lazy writes/sec - Is this because of the above statement?
Thanks
October 29, 2013 at 5:36 am
No, that's describing the write portion of scatter-gather IO, not a read operation.
Why would you expect to see an increase in lazy writes/sec when all you're doing are reads? No data modifications, no dirty buffer pages, no writes that could possibly be done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2013 at 5:53 am
GilaMonster (10/29/2013)
No, that's describing the write portion of scatter-gather IO, not a read operation.Why would you expect to see an increase in lazy writes/sec when all you're doing are reads? No data modifications, no dirty buffer pages, no writes that could possibly be done.
As far as I understood, the lazywrites/sec counter measured the amount per second that pages (clean or dirty) were removed from the buffer pool, thereby freeing up buffers. This is obviously wrong and it means that the lazywrites/sec counter is only a measure of taking a dirty page from cache, writing its changes to disk and freeing up the associated buffer.
If I have a system with absolutely no data modifications but with tons of reads, the lazywrites/sec counter will always report 0. Even though it is removing clean pages from the buffer pool to maintain a free decent amount of free buffers. Correct? Is there a counter to measure this?
October 30, 2013 at 6:13 am
GilaMonster (10/29/2013)
No, that's describing the write portion of scatter-gather IO, not a read operation.Why would you expect to see an increase in lazy writes/sec when all you're doing are reads? No data modifications, no dirty buffer pages, no writes that could possibly be done.
Is the read-ahead what would explain a 0 reading for freelist-stalls/sec on a system where there is memory pressure?
October 30, 2013 at 7:36 am
No. That'll be an efficient lazy writer (freeing up buffers before they're needed)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply