3

I have slow query execution in my SQL Server 2016.

Server configurations:

  1. Total Physical Memory : 128 GB
  2. SQL server Max Memory : 102 GB
  3. Transaction replication is enabled.
  4. Database Size:1.6 TB
  5. Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

I ran the ring buffer query to check if Physical memory is low. The reason I checked for ring buffer for memory pressure is because my plan cache is also getting cleared frequently even though no one is running any script to clear cache.I get the below result where I see HIGH and LOW with same values for Avail Phys Mem.KB.

One thing I learned is for all RESOURCE_MEMPHYSICAL_LOW the indicator process is 2, which means memory issue is in the memory allocated to SQL Server not the rest of memory available for OS and other application. FYI. This is a SQL Server dedicated server.

enter image description here

I also checked in Performance Monitor for Avaialable MBytes which shows the value 16,662.00 (16 GB) as below.

I think 16 GB is enough for OS+other applications, this being a SQL server dedicated server.

enter image description here

Checked the server diagnostics as well. It says that 25 GB is available.

enter image description here

So now I am confused whether:

  1. Is there a memory pressure in this server?
  2. The RESOURCE_MEMPHYSICAL_LOW flag is being set by the OS or external process or SQL Server?
  3. Should I enable LPIM to fix this issue?

Additional details as per @Shanky's answer.

  1. I had Query store enabled, but I disabled it a week ago.
  2. rounds_count for SQL Server Plans with clock hand HAND_EXTERNAL is increasing.

More Details.

  • I am facing huge memory grant by some queries and this is causing RESOURCE_SEMAPHORE waits for other queries.

Does this huge memory grant(one query i checked ,memory grant is ~7GB) result in a memory pressure from SQL server and can this be the cause of plan cache clearing and RESOURCE_MEMPHYSICAL_LOW flag.

| improve this question | |
  • 3
    Pretty much any time you are asking yourself if you should enable LPIM, you should enable LPIM. Make sure this server is not responsible for running other services or applications and just do it. With 16 GB reserved for the OS, there's just no reason to make it easy for SQL Server to surrender memory. Especially when your data is 10X physical memory - it's never going to fit into memory anyway, but don't add pain. – Aaron Bertrand♦ Oct 23 '18 at 18:13
  • Are you saying not to enable LPIM ?I got confused at the last line but don't add pain. – @AaronBertrand – user9516827 Oct 23 '18 at 20:07
  • By "adding pain" I mean letting SQL Server giving up memory, making the problem even worse. Enable the thing. – Aaron Bertrand♦ Oct 23 '18 at 20:16
  • You did not posted most important thing what is output of select @@version – Shanky Oct 24 '18 at 4:14
  • @Shanky added that information. – user9516827 Oct 24 '18 at 13:12
3

is there a memory pressure in this server.

With the limited information, I cannot tell with surety. Need more parameters and output from perfmon to tell about memory pressure. But yes, from the query output you posted for the time 2018-10-23 11:22:30:457 there was indeed low memory for some process(s), not the SQL Server system as whole.

the RESOURCE_MEMPHYSICAL_LOW flag is being set by the OS or external process or SQL server.?

Look at the output it says RESOURCE_MEMPHYSICAL_LOW has values of 2 from IndicatorProcess which means certain processe(s) which were running were indeed facing memory pressure, if IndicatorSystem would have had value of 2 it would have been system wide memory pressure. Also note the memory pressure is coming from low physical(RAM) not low virtual memory(VAS). So you can see just taking a value for one piece of time it is difficult to say there is continuous memory pressure. I suggest you read Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server to understand what the values Indicatorsystem and IndicatorProcess means

should i enable LPIM to fix this issue.?

LPIM is not going to fix the issue it is only going to mask the issue and would come as workaround. You need to find what is causing resource monitor to flag low memory notification, it could be possible that some process running is causing it. Now plan cache getting frequently cleared is something lot of people have reported from SQL Server 2016 onward and it may be due to query store, are you using one ?. Query store used to be culprit behind plan cache clearing but since you are on latest SP I doubt this factor.

For seeing memory pressure for cache we can also use DMV sys.dm_os_memory_cache_clock_hands

SELECT  *
FROM sys.dm_os_memory_cache_clock_hands
WHERE rounds_count > 0

If rounds_count is increasing this means there was pressure which forced clocks to sweep the cache more frequently and remove the entries.

EDIT:

I am facing huge memory grant by some queries and this is causing RESOURCE_SEMAPHORE waits for other queries.Does this huge memory grant(one query i checked ,memory grant is ~7GB) result in a memory pressure from SQL server and can this be the cause of plan cache clearing and RESOURCE_MEMPHYSICAL_LOW flag.

Yes quite likely, I have seen you raised specific thread for this question and I would only add that before you go for anything make sure you have stats updated and indexes defragmented. An outdated stats may lead to bad plan which can end up asking more memory and hence semaphore waits.

Below are list of threads talking about similar issue

| improve this answer | |
  • I updated more details as per your answer.Please go through it. – user9516827 Oct 24 '18 at 16:30
  • Hi is the rounds_count increasing only for external hands or for internal hands only and I believe the value for removed_all_rounds_count should also be increasing ? – Shanky Oct 24 '18 at 17:14
  • yes only happening for external hands and the value is increasing for removed_all_rounds_counts also for external hands. – user9516827 Oct 24 '18 at 17:24
  • since i have IndicatorProcess as 2 it means certain process is facing memory pressure like you mentioned.So there is a possibility that this process can be SQL server right?Because now i noticed a blocking operation at 13:14 and at the same time i see in RESOURCE_MEMPHYSICAL_LOW flag set. – user9516827 Oct 24 '18 at 17:31
  • 1
    IndicatorProcess=2 means SQL Server internally detected memory pressure and signaled low physical memory. This may mean physical memory pressure. if you keep seeing this every time you run the query its quite likely you have memory pressure and this definitely may be clearing caches. LPIM may only prevent excessive trimming. You need to add more RAM or find process which is doing. If this happens all the time then you have memory pressure and adding RAM is what you should do – Shanky Oct 24 '18 at 18:33

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.