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
Thank you sue for your quick response.
Can you please provide the tsql script for top 10 memory query and also,which query occupying more memory on the server ? I tried to find out but no luck.