Analyzing Memory Pressure Using SQL Server

Memory (or simply RAM) is the memory or information storage in a computer that is used to store running programs and data for the programs. The default setting for min server memory is 0, and the default setting for max server memory(sp_configure max server memory) is 2,147,483,647 megabytes (2TB).

SQL Server Best Memory Practices:

Min server memory: 

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed.

Max server memory: 

To prevent Microsoft SQL Server from consuming too much memory, set Max server memory.

Usually it is 70% of total Memory.

Query to find assigned memory to SQL:

Query 1:

SELECT physical_memory_kb,physical_memory_kb/1024 as mb FROM sys.dm_os_sys_info;

EXEC sp_configure 'max server memory';

Query 2:

SELECT object_name, cntr_value,cntr_value/1024 as mb
  FROM sys.dm_os_performance_counters
-- WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');
  WHERE 
   counter_name ='Target Server Memory (KB)' or 
   counter_name = 'Total Server Memory (KB)' 

When the Total Server Memory and Target Server Memory values are close to each other, there’s no memory pressure on the server.

Analyze Memory Using DMO:(sys.dm_os_ring_buffers)

The sys.dm_os_ring_buffers DMV is used to diagnose system wide problem in SQL Server. It is also used to troubleshoot connectivity error, track deadlock, monitor system health, CPU, RAM and Disk Utilization etc by using this dmo.

According to Microsoft link, taking advantage of the dynamic management object (DMO) sys.dm_os_ring_buffers might be a great way to get you that quick and accurate determination if you have memory problems and if they’re coming from the Windows operating system or within SQL Server. To get information about SQL Server memory usage you need to query sys.dm_os_ring_buffers and filter out for "RING_BUFFER_RESOURCE_MONITOR" as used in below query:

/******************************************/

SELECT 
    EventTime,
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
    record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') as [IndicatorsProcess],
    record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') as [IndicatorsSystem],
    record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail Phys Mem, Kb],
    record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb]
FROM (
    SELECT
        --select 6610904/1024 =6455
        --select 137423795704/1024=134202925.492187
        DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
        CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    CROSS JOIN sys.dm_os_sys_info
    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC;

Output:-

Meaning of output:
The Type column is to be read as follows:

• RESOURCE_MEM_STEADY
• • No memory issues (the order above is inconsistent due to the timestamps, but these usually follow a memory low condition to say that Windows is happy with the new memory level achieved)


• RESOURCE_MEMPHYSICAL_LOW
• • Windows is running low on memory and SQL Server must return some


• RESOURCE_MEMPHYSICAL_HIGH
• • Windows has spare memory and SQL Server can take some more if required

If the IndicatorsSystem value is greater than zero then this means that the memory situation was server-wide.

The IndicatorsProcess value means that it was a specific process which ran into the memory condition and can be one of 3 values:

• 1 = High Physical Memory
• 2 = Low Physical Memory
• 3 = Low Virtual Memory

Note: 

If available memory is constantly low and server load cannot be reduced, it’s necessary to add more RAM.