Sue_H - Saturday, October 21, 2017 8:35 AM

Database admin(DBA) - Thursday, October 19, 2017 1:09 PM

Hi All,
Need your help to understand that my DB server is require more memory or not as per the below counters which I have observed some time apart from top 10 memory & long running queries.
As per my understanding below target and total server memory values are different and how should I understand that my DB server is optimal ? and PLE value also different?

Memory:
Available Mbytes     421.002
Page Faults/sec        2.434.009
Pages Input/sec      40.002
Page output/Sec      22.987

SQL server:Buffer Manager
Buffer Cache hit ratio    99.92
Lazy writes/sec       0.00
Page life expectancy  9.333.458
Page reads/sec     3.851
Pages writes/sec 5.072

SQL server :Memory Manager
 Memory Grants pending 0.00000
Target server memory  (KB) 11.688,920.297
Total server memory(KB)   11,674,150.049

Please help me to understand more efficient way as per above perform counets?
Any help would be greatly appreciated.

Thanks in advance!!
Best Regards,
DBA

For a lot of these numbers, there appears to be a mix of commas and dots in terms of representing the numbers. If you round the numbers to whole numbers, I would guess your PLE isn't really 9 and your target server memory isn't 12.
If target server memory is 11,688,920,297 and total server memory is 11,674,150,049 the ratio is fine. You won't see them be exactly the same.
Not sure what you mean by PLE being different. The formula often seen for PLE (if you ignore the 300 which you should)  is GB Max memory / 4 * 300
You can find some of these referenced in the following article -
Top SQL Server Memory Pressure Counters

Sue