3 Quick Memory Troubleshooting Tips for SQL Server
There are many misconceptions about SQL using memory (RAM) on the physical server. The most common one I hear is that a user is worried about the Server RAM being close to maxed out. SQL Server is designed to use as much memory as it can. The only limits are how much memory the instance is set to cap at (Max Memory) and how much RAM is actually on the server.
For example, imagine your SQL server runs optimally with only 8GB of memory and the server shows ~95% of total RAM being used. You can double the RAM on the machine, double the SQL instance’s Max Memory setting, and then watch the server slowly climb back to 95%. This isn’t necessarily a problem. SQL is just caching as much temp data as it can with what is given.
Below are my quick go-to investigation points to determine if there is actually a memory issue or if SQL Server is just doing what it’s supposed to do:
Verify Max Memory setting from the instance properties and compare it to the Server Total Memory. SQL should be given as much as possible, but each environment is different. There are also many factors to take into consideration (# of instances, applications, workload, cluster status, etc.) At the very least, make sure that some GB are left for the operating system. Additionally, ensure anything else that needs it is on this machine.
If your Max Memory is set to 2147483647, change it right now. This is the default value that SQL installs with, telling it to use as much as it needs. This can cause performance issues for the OS and other applications on the server and slow everything down if it ever bottlenecks.
Run the built-in Memory Consumption Report from the instance properties. The healthy details to immediately look for are a high PLE value and low Memory Grants Pending value. Page Life Expectancy is the number of seconds a page will stay in the buffer pool before releasing to ‘reuse’ memory on the server. A general recommendation is to have 300 seconds or higher, but this recommendation is exponentially increased when there is a higher amount of RAM on the server. Memory Grants Pending is the number of processes waiting for a workspace memory grant. Zero is the best value since it means everything running is able to do so with the sufficient amount of memory that it needs.
Run the below query to check the current memory counters. The third Result Set will show a timestamp of when the memory change occurred. Keep an eye out for any ‘low’ memory alerts, and from there on, determine if memory pressure should be investigated further if SQL is utilizing the appropriate amount.
SELECT @@SERVERNAME AS [Server Name]
,total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)]
,available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)]
,total_page_file_kb / 1024 AS [Total Page File Memory (MB)]
,available_page_file_kb / 1024 AS [Available Page File Memory (MB)]
,system_memory_state_desc AS [Available Physical Memory]
,CURRENT_TIMESTAMP AS [Current Date Time]
FROM sys.dm_os_sys_memory
OPTION (RECOMPILE);
GO
SELECT physical_memory_in_use_kb / 1024 AS [Physical Memory In Use (MB)]
,locked_page_allocations_kb / 1024 AS [Locked Page In Memory Allocations (MB)]
,memory_utilization_percentage AS [Memory Utilization Percentage]
,available_commit_limit_kb / 1024 AS [Available Commit Limit (MB)]
,CASE WHEN process_physical_memory_low = 0 THEN ‘No Memory Pressure Detected’ ELSE ‘Memory Low’ END AS ‘Process Physical Memory’
,CASE WHEN process_virtual_memory_low = 0 THEN ‘No Memory Pressure Detected’ ELSE ‘Memory Low’ END AS ‘Process Virtual Memory’
,CURRENT_TIMESTAMP AS [Current Date Time]
FROM sys.dm_os_process_memory
OPTION (RECOMPILE);
GO
WITH RingBuffer
AS (
SELECT CAST(dorb.record AS XML) AS xRecord
,dorb.TIMESTAMP
FROM sys.dm_os_ring_buffers AS dorb
WHERE dorb.ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’
)
SELECT xr.value(‘(ResourceMonitor/Notification)[1]’, ‘varchar(75)’) AS Notification
,CASE
WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 1
THEN ‘High Physical Memory Available’
WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 2
THEN ‘Low Physical Memory Available’
WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 4
THEN ‘Low Virtual Memory Available’
ELSE ‘Physical Memory Available’
END AS ‘Process Memory Status’
,CASE
WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 1
THEN ‘High Physical Memory Available’
WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 2
THEN ‘Low Physical Memory Available’
WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 4
THEN ‘Low Virtual Memory Available’
ELSE ‘Physical Memory Available’
END AS ‘System-Wide Memory Status’
,DATEADD(ms, – 1 * dosi.ms_ticks – rb.TIMESTAMP, GETDATE()) AS NotificationDateTime
FROM RingBuffer AS rb
CROSS APPLY rb.xRecord.nodes(‘Record’) record(xr)
CROSS JOIN sys.dm_os_sys_info AS dosi
ORDER BY NotificationDateTime DESC;
Thanks for reading! Leave a comment below if you have tried these tips.