asked
Memory usage alert
Hi All, We have a environment with 512GB of RAM and have given MAXSERVER MEMORY as 450GB. I am looking for a script to find the below components and need an alert mechanism depending up on it. -> Total Physical Memory in OS level. -> Total Available Memory in OS level. -> Total Max Server Memory set to SQLSERVER. -> Total Available Memory for SQLSERVER among the 450GB of MAXSERVER MEMORY. -> Total available free space among used memory of sql server. The output of the script as below. 1. Total Available OS Physical Memory 2. SQL Server MAXServer Memory 3. Total Available OS Physical Memory 4. Total Available SQLSERVER Memory among Total sql server maxserver memory 5. Total Free Space Among Used Memory by SQLSERVER. Please share any script is available for monitoring the above. I have a following doubts can anyone clarify the same. -> We have given 450GB MAXMEMORY to SQL SERVER among the total physical memory of 512GB. The SQLSERVER already occupies 446GB among 450GB but used is only 440GB another 46GB is free space. This information grabbed from IDERA. -> How we can will get the informatoin as above by script. Environment details: SQLSERVER 2014 PHYSICAL SERVER MEMORY: 512GB MAXSERVER MEMORY: 450GB Regards, Narendra.
This lot should help you get started. Memory information in SQL Server - have a look at [`sys.dm_os_sys_info`][1] SELECT * FROM sys.dm_os_sys_info For memory allocated to SQL Server - check [`sys.configurations`][2] SELECT * FROM sys.configurations WHERE name = 'max server memory (MB)'; For memory being *used* by SQL Server, check [`sys.dm_os_process_memory`][3]: 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 FROM sys.dm_os_process_memory; The above query was taken from the MSDN article ["Monitor Memory Usage"][4] [1]:
https://msdn.microsoft.com/en-us/library/ms175048.aspx [2]:
https://msdn.microsoft.com/en-us/library/ms188345.aspx [3]:
https://msdn.microsoft.com/en-us/library/bb510747.aspx [4]:
https://msdn.microsoft.com/en-GB/library/ms176018.aspx
Thanks Thomas, Can you clarify the below ones. The outputs as below Total_VAS_IN_MB: 134217727 Memory_Used_Sqlserver_MB: 450366 MaxServerMemory: 450000 Locked_pages_used_Sqlserver_MB: 0 What exactly the above output is saying, Because the MaxServerMemory is < Memory_Used_SqlServer_MB. I think sqlserver occupies 450366MB of space but not used that much. So what is the free space available among Sqlserver Used Memory.
19 People are following this question.
