trouble shoot Lazy writer

Question

trouble shoot Lazy writer

ramyours2003

SSChampion

Points: 12268
More actions
December 19, 2016 at 3:13 am

#332807

How to trouble shoot the lazy writer issues ?

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

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

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

What's the problem?

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

ramyours2003 SSChampion Points: 12268 More actions · Answer 2

no problem in my system but I would like to know how can we trouble shoot the lazy writer issues ?

Abdallah.mehdoini SSC Veteran Points: 221 More actions · Answer 3

The lazy writer process periodically checks the available free space in the buffer cache between two checkpoints. If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache

If SQL Server needs more memory and the buffer cache size is below the value set as the Maximum server memory parameter for the SQL Server instance, the lazy writer will take more memory

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

ramyours2003 (12/19/2016)
no problem in my system but I would like to know how can we trouble shoot the lazy writer issues ?

There's not really much configuration you can do to the lazy writer, so the biggest part of any troubleshooting would be to identify the root cause and make sure that it really is a problem with the lazy writer and not something else (and most likely it would be something else)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Abdallah.mehdoini (12/19/2016)
The lazy writer process periodically checks the available free space in the buffer cache between two checkpoints. If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache
If SQL Server needs more memory and the buffer cache size is below the value set as the Maximum server memory parameter for the SQL Server instance, the lazy writer will take more memory

Copied from here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden