Sql-server – Does enabling LPIM work even if the memory pressure is coming from SQL Server itself

memoryperformanceperformance-tuningplan-cachesql serversql-server-2016

I have a scenario where I have 128 GB Physical memory for the SQL Server 2016 machine.
It is a SQL Server dedicated machine.
I have 110 GB allocated to SQL Server itself.

I have performance issues when the CPU usage is high or the no of connections go high. It is normal for the CPU usage to go high as there are more request coming in with more connections.
I looked at the RING BUFFERS,plan cache etc. I see that plan cache gets cleared often when i have more usage. Also RING BUFFER indicated memory pressure. I see that for most of the cases IndicatorProcess=2 which means SQL server internally detected memory pressure. On few occasions it is IndicatorSystem = 2.

I have been struggling with this for sometime now. I have tuned the frequently executed queries and most of them runs fast when there are plans available in plan cache.
My Question here is :

  1. Does enabling LPIM help in this scenario since i see
    IndicatorProcess=2 in most of the cases.
  2. I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?

Additional Info

enter image description here

Here is what the monitoring tool has to say for the last one hour.

enter image description here

Best Answer

Does enabling LPIM help in this scenario since i see IndicatorProcess=2 in most of the cases.

No.

I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?

Yes.

And

I see that plan cache gets cleared often when i have more usage and most of [the frequent queries] runs fast when there are plans available in plan cache

Suggests you should focus on your plan cache. Are there a large number of single-use plans? If so consider setting the optimize for ad hoc workloads configuration option.

Related Question