We are receiving PLE alerts from SCOM for several instances.

These instances are on hardware servers, and use NUMA nodes. SQL Server 2012 SP1 CU7 Enterprise Edition is installed. Memory is fixed and CPU affinity is configured.

Looking at the buffermanager PLE value, I see that there are indeed regular drops, but looking at the buffer node values, PLE values are OK.

select * 
from sys.dm_os_performance_counters 
where counter_name = 'Page life expectancy'

enter image description here

Following the calculation found on SQLSkills site, the result for the buffer manager PLE is completely wrong.

Besides that, I also noticed that the numa nodes listed in sys.dm_os_performance_counter don't seem to be correct.

For this specific instance we use numa node 2 and 3. Sys.dm_os_schedulers shows the correct information. But according to sys.dm_os_performance_counter numa nodes are 0 and 2.

License is OK, sys.dm_os_sys_info shows 48 CPUs. The errorlog is showing the correct information regarding the licensing.

I would expect the buffer manager PLE value would be in relation to the buffer node values. Also, the numa nodes shown in sys.dm_os_performance_counters should reflect the situation in sys.dm_os_schedulers.

Am I interpreting these numbers incorrectly or is there something wrong in SQL Server?


These are the results for the query:

enter image description here

The reason we are behind on service packs is related to internal company politics. However, we plan to upgrade to SP3 in the near future.
I did some additional checks, and it seems all instances that have the PLE issue are on SP1 CU7. Other instances are on SP2 CU6. And for those who are on SP2 CU6 the buffer manager value matches with the buffer node values.

  • 2
    You do realize you are about three years behind in CU's and Service Packs (and thus out of MSFT support)? Specifically hope you don't do ONLINE index rebuilds, because there is a nasty bug with SP1 CU 11 that you are vulnerable to corruption. – Andrew Loree Mar 9 '16 at 23:47
  • What so the following affinity query show for results? pastebin.com/7qMuv0Ji – Andrew Loree Mar 10 '16 at 0:01
  • I agree with Andrew I suggest you first apply SQL Server 2012 Sp3 and then if you again see the PLE information you can get back to us. reason I am saying this is because there are some memory related fixes in SQL Server 2012 Sp2 CU4 and 7. Also SQL Server team fixed the PLE plummet problem in SQL Server 2012 Sp1 CU4 – Shanky Mar 10 '16 at 5:11

Your Answer

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

Browse other questions tagged or ask your own question.