SQL Server 2012 – How to Monitor RAM/Memory

memorymonitoringsql-server-2012

I'm planning to create a job that automatically gets the status of my server's RAM/memory at the time of the job's activation.

My job has the command:

use master 
    insert into Rec.dbo.MemoryRec
    SELECT  
    (physical_memory_in_use_kb/1024) AS Memory_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 ,
    getdate()
    FROM sys.dm_os_process_memory;

Is this enough to monitor a server's RAM? Are there better queries that can be used?

Ps Please, as much as possible, do not suggest 3rd party programs because I currently cannot afford things like those

Best Answer

Is this enough to monitor a server's RAM? Are there better queries that can be used?

If your requirement is just to check SQL Server memory usage then the query used is fine with slight modification. 

 use master 
    insert into Rec.dbo.MemoryRec
    SELECT  
    (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,  
    (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, 
    (virtual_address_space_committed_kb/1024) as Total_Mem_used_RAMAndPagefile 
    (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
    ((virtual_address_space_committed_kb-physical_memory_in_use_kb)/1024) as SQLServerMemory_InPagefile
    process_physical_memory_low,  
    process_virtual_memory_low ,
    getdate()
    FROM sys.dm_os_process_memory;

Do not forget to add new columns to tablerec.dbo.MemoryRec or the query will start failing.

The column alias Total_Mem_used_RAMAndPagefile tracks Total memory used by SQL Server RAM+Page file. And if you subtract this value from physical_memory_in_use_kb it will give you amount of memory used by SQL Server process in page file. Or simply how much SQL Server is getting paged. Please note on normal system there would always be some amount of paging so do not worry.

If your requirement is to see if SQL Server instance is facing memory pressure then you need to take help of perfmon counters. Let me know what you need

Related Question