I have a scenario where I have 128 GB Physical memory for the SQL Server 2016 machine.
It is a SQL Server dedicated machine.
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 :
- Does enabling LPIM help in this scenario since i see
IndicatorProcess=2in most of the cases.
- I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?
Here is what the monitoring tool has to say for the last one hour.