1

Can anyone please explain me below situation we have and please suggest me solution for how to deal with Memory issues in SQL Server 2008R2 Version.

Installed RAM 128 GB

FYI... I have assigned close to 122 GB for SQL Server.

Still please clarify what this means :

SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold
  1. I see PLE more than 300 which is good ?
  2. Please clarify other Performance counters if the value is good or needs to be addressed.

enter image description here

Improve this question
4
  • 1
    Why have you tagged your question to SQL Server 2014 ?. PLE=300 is old value it simply does not signify anything with 122 G RAM on system. Please tag correctly
    – Shanky
    Commented Nov 15, 2016 at 7:30
  • From where did you got the alert ? Do you have any monitoring system in place
    – Shanky
    Commented Nov 15, 2016 at 7:48
  • Yes @Shanky,we have SCOM and other monitoring system in place.I have removed the SQL Server 2014 tag.
    – Learning_Learning
    Commented Nov 15, 2016 at 7:51
  • 1
    This error is from SCOM, have you updated to the latest managment packs? as @Shanky states in his answer the PLE mark of 300 is not relevant anymore and you really should change the operating threshold for the SCOM test.
    – Spörri
    Commented Nov 15, 2016 at 8:57

1 Answer 1

Reset to default
3

SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold

This does not really indicates a problem. What it means is SQL Server has issues more number of physical read and writes than the threshold you have set. Now next question is how often do you get the alerts, if this is often you might need to find out whether you are facing memory crunch and what queries running are causing this.

I see PLE more than 300 which is good ?

300 mark should be completely ignored unless you have system with 4 GB memory. The base PLE should be calculated like (DataCacheSizeInGB/4GB *300). This should be your benchmark PLE anything much lower than that should bother you. Moreover if you have NUMA system you have to track PLE for each NUMA node

Please clarify other Performance counters if the value is good or needs to be addressed.

The counters should not be taken for a moment collect it for period of 8 hours or so and please run it when load on system is on peak. You can use Performance data collector set

Improve this answer
3
  • in your formula (Max server memory*4)/300 what is the unit of memory? KB, MB or GB? Where did you get the formula from?
    – John
    Commented May 26, 2019 at 23:18
  • @Asg The formula is widely accepted. This is the source. The memory value is in GB.
    – Shanky
    Commented May 27, 2019 at 6:32
  • The 300sec is a nod to Jim Gray's famous Five Minute Rule. en.wikipedia.org/wiki/Five-minute_rule
    – David Browne - Microsoft
    Commented May 29, 2019 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.