SQL Server Database Memory Usages Check

If we check performance of our windows server, and if we saw that the memory consumption is higher,  70% upper, check the sql server memory usages, by:

  * Open Task Manager

  * Open Tab Processes

  * Find sqlservr.exe process, by sort it by memory
  * If you see there is top highest memory usages, please check through sql server management studio
 
Below some queries to check or monitor sql server database memory usages:

To get total memory usages:
    SELECT  
    (physical_memory_in_use_kb/1024) AS Mem_usedby_Sqlserver_MB,  
    (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
    (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
    process_physical_memory_low,  
    process_virtual_memory_low  
    FROM sys.dm_os_process_memory;  
 

 

To get status memory usages:
    select
          total_physical_memory_kb/1024 AS total_physical_mem_mb,
          available_physical_memory_kb/1024 AS available_physical_mem_mb,
          total_page_file_kb/1024 AS total_page_file_mb,
          available_page_file_kb/1024 AS available_page_file_mb,
          100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))
          AS 'Percent_Used',
          system_memory_state_desc
    from  sys.dm_os_sys_memory;

To get memory usages on each database:
    DECLARE @tot_buffer INT;
    SELECT  @tot_buffer = cntr_value
    FROM   sys.dm_os_performance_counters
    WHERE  RTRIM([object_name]) LIKE '%Buffer Manager'
           AND counter_name = 'Database Pages';
    
    ;WITH DBBuffer AS
    (
    SELECT  database_id,
            COUNT_BIG(*) AS db_buffer_pages,
            SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
    FROM    sys.dm_os_buffer_descriptors
    GROUP BY database_id
    )
    SELECT
           CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS 'db_name',
           db_buffer_pages AS 'db_buffer_pages',
           db_buffer_pages / 128 AS 'db_buffer_Used_MB',
           [mbempty] AS 'db_buffer_Free_MB',
           CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @tot_buffer) AS 'db_buffer_percent'
    FROM   DBBuffer
    ORDER BY db_buffer_Used_MB DESC;