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

  • 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 Nov 15 '16 at 7:30
  • From where did you got the alert ? Do you have any monitoring system in place – Shanky Nov 15 '16 at 7:48
  • Yes @Shanky,we have SCOM and other monitoring system in place.I have removed the SQL Server 2014 tag. – SQLBoy Nov 15 '16 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 Nov 15 '16 at 8:57
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

  • 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? – Asg May 26 at 23:18
  • @Asg The formula is widely accepted. This is the source. The memory value is in GB. – Shanky May 27 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 May 29 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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