How Can I Control "Buffer Manager Free Pages" Spikes

Question

How Can I Control "Buffer Manager Free Pages" Spikes

Kenneth Igiri

SSCarpal Tunnel

Points: 4998
More actions
June 26, 2015 at 11:24 am

#305405

We have Foglight deployed on our SQL Server instance. A particular instance used for a SCOM installation keeps throwing these kinds of errors:
Buffer Pages Free. Only XX% of the buffer pages are free.
Sometimes 12% , 17%, 19% etc (the threshold being 20%). I have tried increasing Max Server Memory to 40G and the PLE , Lazy Writes / Sec etc do not indicate memory pressure when I check perfmon.
What can I do to minimize occurence of these spikes? That is to ensure there is ALWAYS more than 20% free pages?
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

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

Why would you want more than 20% of you memory to not be used? Surely the point is to ensure that the data cache is used, not that SQL is leaving 20% of it unallocated?

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

Kenneth Igiri SSCarpal Tunnel Points: 4998 More actions · Answer 2

GilaMonster (6/26/2015)
Why would you want more than 20% of you memory to not be used? Surely the point is to ensure that the data cache is used, not that SQL is leaving 20% of it unallocated?

Thanks GilaMonster. I figured too. Would that mean the wise thing to do is modify the threshold on the monitoring tool?

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

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

If you don't feel like the alert is useful, disable it.

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

Kenneth Igiri SSCarpal Tunnel Points: 4998 More actions · Answer 4

GilaMonster (6/27/2015)
If you don't feel like the alert is useful, disable it.

OK. Thanks. Will consider that option.

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising