{{announcement.body}}
{{announcement.title}}

3 Quick Memory Troubleshooting Tips for SQL Server

DZone 's Guide to

3 Quick Memory Troubleshooting Tips for SQL Server

If you're having trouble with or have concerns about the memory in your SQL server, use these tips for quick troubleshooting.

· Database Zone ·
Free Resource

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 the 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 (the # of instances, applications, workload, cluster status, etc.) At the very least, make sure that some GBs 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.

Max server memory

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.  

Memory consumption menu
Memory consumption

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.


SQL
 




x
52


1
SELECT @@SERVERNAME AS [Server Name]
2
,total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)]
3
,available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)]
4
,total_page_file_kb / 1024 AS [Total Page File Memory (MB)]
5
,available_page_file_kb / 1024 AS [Available Page File Memory (MB)]
6
,system_memory_state_desc AS [Available Physical Memory]
7
,CURRENT_TIMESTAMP AS [Current Date Time]
8
FROM sys.dm_os_sys_memory
9
OPTION (RECOMPILE);
10
GO
11
SELECT physical_memory_in_use_kb / 1024 AS [Physical Memory In Use (MB)]
12
,locked_page_allocations_kb / 1024 AS [Locked Page In Memory Allocations (MB)]
13
,memory_utilization_percentage AS [Memory Utilization Percentage]
14
,available_commit_limit_kb / 1024 AS [Available Commit Limit (MB)]
15
,CASE WHEN process_physical_memory_low = 0 THEN ‘No Memory Pressure Detected’ ELSE ‘Memory Low’ END AS ‘Process Physical Memory’
16
,CASE WHEN process_virtual_memory_low = 0 THEN ‘No Memory Pressure Detected’ ELSE ‘Memory Low’ END AS ‘Process Virtual Memory’
17
,CURRENT_TIMESTAMP AS [Current Date Time]
18
FROM sys.dm_os_process_memory
19
OPTION (RECOMPILE);
20
GO
21
WITH RingBuffer
22
AS (
23
SELECT CAST(dorb.record AS XML) AS xRecord
24
,dorb.TIMESTAMP
25
FROM sys.dm_os_ring_buffers AS dorb
26
WHERE dorb.ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’
27
)
28
SELECT xr.value(‘(ResourceMonitor/Notification)[1]’, ‘varchar(75)’) AS Notification
29
,CASE
30
WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 1
31
THEN ‘High Physical Memory Available’
32
WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 2
33
THEN ‘Low Physical Memory Available’
34
WHEN xr.value(‘(ResourceMonitor/IndicatorsProcess)[1]’, ‘tinyint’) = 4
35
THEN ‘Low Virtual Memory Available’
36
ELSE ‘Physical Memory Available’
37
END AS ‘Process Memory Status’
38
,CASE
39
WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 1
40
THEN ‘High Physical Memory Available’
41
WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 2
42
THEN ‘Low Physical Memory Available’
43
WHEN xr.value(‘(ResourceMonitor/IndicatorsSystem)[1]’, ‘tinyint’) = 4
44
THEN ‘Low Virtual Memory Available’
45
ELSE ‘Physical Memory Available’
46
END AS ‘System-Wide Memory Status’
47
,DATEADD(ms, – 1 * dosi.ms_ticks – rb.TIMESTAMP, GETDATE()) AS NotificationDateTime
48
FROM RingBuffer AS rb
49
CROSS APPLY rb.xRecord.nodes(‘Record’) record(xr)
50
CROSS JOIN sys.dm_os_sys_info AS dosi
51
ORDER BY NotificationDateTime DESC;
52
 
          
6
,system_memory_state_desc AS [Available Physical Memory]



Results


Thanks for reading! Leave a comment below if you have tried these tips. 

 

Topics:
database ,database administration ,memory ,sql server ,sql troubleshooting

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}