The memory problems I see most often:

• Memory Pressure:
• External Memory Pressure: High memory usage by other applications on the server can force SQL Server to reduce its memory usage
• Internal Memory Pressure: SQL Server components, such as the buffer pool or query execution, can consume excessive memory, leading to performance degradation
• Insufficient Memory for Queries:
• Errors like 701 (failure to allocate sufficient memory to run a query) and 8645 (failure to execute a query due to no memory for memory grants) indicate that SQL Server cannot allocate enough memory for query execution
• Memory Grants Issues:
• Memory Grants: During query execution, SQL Server allocates memory for operations like sorting and hashing. If these grants are too large or frequent, they can cause memory pressure
• Fragmentation in Memory:
• High Virtual Log File (VLF) counts can lead to fragmentation within the transaction log, affecting write performance
• Memory Leaks:
• Memory leaks can occur when SQL Server fails to release memory after use, leading to gradual memory consumption and eventual performance issues
• Configuration Issues:
• Incorrect memory settings, such as max server memory or min server memory, can lead to inefficient memory usage

To gather basic information about the operating system’s memory amounts and state in SQL Server, you can use the Dynamic Management Views (DMVs). These queries are useful:

• Total and Available Physical Memory:

SELECT
    total_physical_memory_kb / 1024 AS TotalMemoryMB,
    available_physical_memory_kb / 1024 AS AvailableMemoryMB,
    system_memory_state_desc AS MemoryState
FROM sys.dm_os_sys_memory;

• Memory Usage by SQL Server:

SELECT
    physical_memory_in_use_kb / 1024 AS SQLServerMemoryMB,
    large_page_allocations_kb / 1024 AS LargePageAllocationsMB,
    locked_page_allocations_kb / 1024 AS LockedPageAllocationsMB,
    total_virtual_address_space_kb / 1024 AS TotalVirtualAddressSpaceMB,
    virtual_address_space_reserved_kb / 1024 AS VirtualAddressSpaceReservedMB,
    virtual_address_space_committed_kb / 1024 AS VirtualAddressSpaceCommittedMB,
    virtual_address_space_available_kb / 1024 AS VirtualAddressSpaceAvailableMB
FROM sys.dm_os_process_memory;

• Memory Clerks Information:

Between them they give a snapshot of how the instance is using memory.

SELECT
            type,
            SUM(virtual_memory_reserved_kb) / 1024 AS ReservedMemoryMB,
            SUM(virtual_memory_committed_kb) / 1024 AS CommittedMemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type;

Getting the memory settings right matters for both performance and stability. What I recommend:

• Set Appropriate Max and Min Server Memory:
• Max Server Memory: Configure this setting based on the total physical memory of the system. Typically, allocate around 75-80% of the total system memory to SQL Server, leaving the rest for the OS and other applications
• Min Server Memory: Set this to make sure SQL Server has a guaranteed minimum amount of memory, preventing it from dropping below a certain threshold
• Enable Lock Pages in Memory (LPIM):
• This setting prevents SQL Server’s memory from being paged out by the operating system, which hurts performance badly
• Monitor Memory Usage Regularly:
• Use DMVs like sys.dm_os_sys_memory and sys.dm_os_process_memory to monitor memory usage and adjust configurations as needed
• Configure Autogrowth Settings:
• Set autogrowth to a fixed size (e.g., 512 MB or 1 GB) rather than a percentage to control the number of Virtual Log Files (VLFs) created during each growth event
• Optimise Query Performance:
• Regularly review and optimise queries to make sure they are not consuming excessive memory. Use tools like the Query Store and DMVs to identify and

address problematic queries

• Regular Maintenance:
• Perform regular maintenance tasks such as index rebuilding and statistics updates to make sure efficient memory usage

-- Good basic information about OS memory amounts and state (System Memory)
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],

       available_physical_memory_kb/1024 AS [Available Memory (MB)],
       total_page_file_kb/1024 AS [Total Page File (MB)],
       available_page_file_kb/1024 AS [Available Page File (MB)],
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM  sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure

From my SQL Server DBA handbook.