High lazywrites - what other counters to look for

Question

High lazywrites - what other counters to look for

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
June 6, 2018 at 4:51 pm

#351427

My current server PLE seems to be good about 2000, however is see high spike in lazywrites/sec , like 4K to 10K every few seconds. It goes to 20K and then drops down to 0, i have been banging my head for days trying to figure out what is causing that. If PLE is good why are lazywrites even happening. Looking for recommendations to see what other memory related counters should i track down. TIA

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

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

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

PLE uses a mean of a harmonic mean - see https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/ (which discusses a customer situation with a similar concern to yours, and provides a better metric to observe and provides a more appropriate method to judge whether a PLE is good). Lazywrites occur more quickly than PLE reports (lazywrites occur in response to shorter-term memory needs between checkpoints).

Sue_H SSC Guru Points: 90860 More actions · Answer 2

curious_sqldba - Wednesday, June 6, 2018 4:51 PM
My current server PLE seems to be good about 2000, however is see high spike in lazywrites/sec , like 4K to 10K every few seconds. It goes to 20K and then drops down to 0, i have been banging my head for days trying to figure out what is causing that. If PLE is good why are lazywrites even happening. Looking for recommendations to see what other memory related counters should i track down. TIA

The spikes are really typical if that's any consolation. Generally you will see Page Life Expectancy, Lazy Writes/sec, Checkpoints/sec monitored together. I would add List Stalls/sec which indicates requests waiting for free pages. The Lazy Writer works on a regular interval so it's not necessarily indicative of memory pressure. You may want to consider that you can see large cache flushes just from some not so well written large queries - e.g. select * from MyTableWithBillionsOfRows.
You may want to check/monitor dm_os_buffer_descriptors to get a better idea of the activity in your buffer pool. You can find tons of different queries in various articles, forums using this DMV to capture all kinds of details - dirty vs clean pages, pages or space per database, etc.

Sue

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

I think people spend way too much time looking at counters. Right click on the instance in the Explorer Window, click on reports, and follow your nose for the real performance problems with the understanding that the performance based reports there are similar to what a lot of people write code for and are subject to when the last time cache was cleared. What you'll find out along the way is that it's not generally the long running queries that are a problem... it's the short, "fast" ones that are called a bazillion times. Fix those and most of your problems simply vanish.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)