2

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

| improve this question | | | | |
  • Can you add output of select @@version in the question. Also add the output of select * from sys.dm_os_process_memory – Shanky Dec 9 '19 at 13:28
  • @Shanky Please see the updated post. – user9516827 Dec 11 '19 at 14:21
2

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.

| improve this answer | | | | |
  • There are single-use plans but there are plans with high execution count as well.optimize for ad hoc workload is set to true already.Forced parametrization is also enabled. – user9516827 Dec 6 '19 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.