SQL Server Buffer Manager – Page Reads/Writes Per Second Exceeded

memorysql-server-2008-r2

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

Best Answer

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

Related Question