Monitoring Lazy Writer

Question

Monitoring Lazy Writer

SQLSACT

SSC-Insane

Points: 21838
More actions
April 9, 2013 at 6:51 am

#276188

Hi All
I have set up a test scenario to monitor the effects of the Lazy Writer by limiting my SQL instance to 100MB and by querying large tables.
Using the below query I can see my Clean page count fluctuate as it reaches 100MB - I am assuming the Lazy writer is doing this to maintain a decent amount of free buffers by removing older pages from the buffer pool
SELECT
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END)*8/1024 AS CleanMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DB_NAME(database_id)
GO
The problem is that while I was monitoring the buffer descriptors, I was also monitoring the instance using Performance Monitors, while monitoring I noticed that there was absolutely no increase in the LazyWrites/sec during the entire test. Why is this? Surely the Lazy Writer must have been doing something?
Any thoughts?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are you just querying large tables or modifying them? If just querying, then you won't see any lazy writer activity as it has nothing that needs writing.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

GilaMonster (4/9/2013)
Are you just querying large tables or modifying them? If just querying, then you won't see any lazy writer activity as it has nothing that needs writing.

I'm just querying, so only dealing with clean pages

To confirm, if free list is low - Lazy Writer will drop clean pages from the buffer pool based on LRU algorithm, will flush changes of dirty pages to disk and drops the page from buffer pool to maintain a decent amout of free buffers.

So the Lazy Writes/sec counter only spikes when the Lazy Writer flushes changes of dirty pages to disk and drops the page from buffer pool.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

SQLSACT (4/9/2013)
GilaMonster (4/9/2013)
Are you just querying large tables or modifying them? If just querying, then you won't see any lazy writer activity as it has nothing that needs writing.
I'm just querying, so only dealing with clean pages

So if you're not changing any pages, why do you expect to see any writes?

The counter is called lazy writes/sec, measuring (as per the docs) "Number of buffers written by buffer manager's lazy writer." It's not a counter that shows you the number of pages that the lazy writer has removed from memory.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

GilaMonster (4/9/2013)
SQLSACT (4/9/2013)
GilaMonster (4/9/2013)
Are you just querying large tables or modifying them? If just querying, then you won't see any lazy writer activity as it has nothing that needs writing.
I'm just querying, so only dealing with clean pages
So if you're not changing any pages, why do you expect to see any writes?
The counter is called lazy writes/sec, measuring (as per the docs) "Number of buffers written by buffer manager's lazy writer." It's not a counter that shows you the number of pages that the lazy writer has removed from memory.

Got it, thanks

Would you say that using LazyWrites\sec as a performance metric on a system just used for Querying data is basically useless then.

It's not a counter that shows you the number of pages that the lazy writer has removed from memory

Is there a counter that will reflect this?

Thanks