I have slow query execution in my SQL Server 2016.
Server configurations:
- Total Physical Memory : 128 GB
- SQL server Max Memory : 102 GB
- Transaction replication is enabled.
- Database Size:1.6 TB
- 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.
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.
Checked the server diagnostics as well. It says that 25 GB is available.
So now I am confused whether:
- Is there a memory pressure in this server?
- The
RESOURCE_MEMPHYSICAL_LOW
flag is being set by the OS or external process or SQL Server? - Should I enable LPIM to fix this issue?
Additional details as per @Shanky's answer.
- I had Query store enabled, but I disabled it a week ago.
- 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.
select @@version
– Shanky Oct 24 '18 at 4:14