Summary
It is a generally a hard question when you want to know whether better memory performance can be achieved by adding more memory especially in a shared SQL environment, where you have limited understanding of various databases and most importantly,
You DO NOT want to understand them!
This article will provide you a very simple guide to answer whether you should attempt adding memory to a production SQL server.
Assumptions
- SQL server hardware is NUMA-aware
- Databases memory usage characteristics are unknown
- SQL servers are slow for unknown reasons but you suspect it is memory related
Solution
This solution is based on the counter “SQL Server:Buffer Node:Page Life Expectancy“, in short, PLE. From Microsoft, the description is “Indicates the minimum number of seconds a page will stay in the buffer pool on this node without references.”
This article is not going to describe what PLE means and what other people are saying. As a take away, we prefer PLE to be high.
Let me introduce two derived metrics:
- Page IO Rate (MB/s) = (Total Pages in a NUMA node / 128) / PLE
- Max Page IO Rate (MB/s) = (Total Pages in a NUMA node / 128) / 3600
The idea is to express how busy the pages (memory) are in a window of 3600 seconds, then we can have one final metric:
- Page Stress % = Page IO Rate / Max Page IO Rate
Feel free to adjust 3600 to any other number so that the following conditions are true:
- The SQL server with the highest Page Stress % is approximately 100% (this is called normalization)
- The values generally match with the performance of SQL servers
- You are satisfied with the scale of the values across all SQL servers
As a rule of thumb, I suggest Page Stress % to be kept below 75%. Interesting enough, Page Stress % can be simplified to just (3600 / PLE)
Download the query Here and thanks Thomas Larock
Conclusion
Now you have such an easy way to know whether you can try getting better performance by adding memory. What can you complain?
sysadmin++ 