SQL Memory Calculation

Question

Hello Experts,

Need your help ! In our environment ,we have a SQL server where Max memory provisioned is 1 TB out of 1.5 TB RAM.

Recently, we were asked to assess servers as part of some modernization project. From memory standpoint, we can see PLE as good value (Somewhere around 50000) but our stake holder raised concern on these PLE values.

From some references, PLE is considered to be best if its value is more than the below calculation

 PLE = (SQL Max memory(GB)/4)*300 

So for our server, Best PLE should be (1000/4)*300 =75000,which is less than PLE reported in our server. However, our DB server is doing well, no issues reported so far but its PLE value is less than the calculation mentioned above.

Question is, do we really need to consider above PLE calculation for 1 TB and more memory. If not, pls help us to get accurate calculations. Thank you !

Regards,

Naren

Answer 1

Hi @Naren

PLE = (SQL Max memory(GB)/4)*300

This calculation is right. If your PLE goes down suddenly, then the first thing should be to check memory allocation. Is it enough amount of size for the SQL Server? If yes, then check the currently executing request, is there any long-running thread that consumes more memory? If yes, then review the currently executing query statement.

Unnecessary memory usage issue should be observed and take a chance to get it optimized with a proper investigation.

• Which objects are using the more buffer cache memory?
• Allotment of object indexes and partitions are accurate and effective.

Users can monitor it with querying on sys.dm_os_performance_counters DMV to find that PLE threshold is crossing the underline or not. If the counter value found is less than your calculation, then it should trigger a mail to the responsible team to observe and monitor the memory pressure-related parameters.

Please refer to this article for more details: Page Life Expectancy (PLE) in SQL Server.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Best PLE should be (1000/4)*300 =75000,which is less than PLE reported in our server.

And that's good that the PLE on your server is high then the calculated value.

PLE = "Page Life expectancy" means how long a cached data page stays in the buffer pool and the longer the better.