If you have a SQL server that has limited memory allocation that is usually defined on the server level in SQL management Studio as below, in this example we have the SQL server limited to 25GB of RAM:
How can we confirm that the memory allocation is not putting pressure on the SQL server, well with a SQL query, to run this start a "New Query" and then paste the code below into the window and click execute:
SQL Query : Memory Pressure Allocation
SELECT
(total_page_file_kb / 1024) AS Total_Page_File_Size_In_MB,
CASE
WHEN (total_page_file_kb / 1024) < (total_physical_memory_kb / 1024) THEN 'WARNING: Page file smaller than RAM'
WHEN (total_page_file_kb / 1024) >= (total_physical_memory_kb / 1024) * 1.5 THEN 'GOOD: Plenty of virtual memory'
ELSE 'OK: Adequate page file size'
END AS Page_File_Status,
((total_page_file_kb - available_page_file_kb) / 1024) AS Used_Page_File_Size_In_MB,
CASE
WHEN ((total_page_file_kb - available_page_file_kb) * 1.0 / total_page_file_kb) > 0.85 THEN 'WARNING: High page file usage'
WHEN ((total_page_file_kb - available_page_file_kb) * 1.0 / total_page_file_kb) > 0.70 THEN 'CAUTION: Moderate page file usage'
ELSE 'GOOD: Normal page file usage'
END AS Page_File_Usage_Status,
(total_physical_memory_kb / 1024) AS Total_Physical_Memory_Size_In_MB,
((total_physical_memory_kb - available_physical_memory_kb) / 1024) AS Used_Physical_Memory_Size_In_MB,
CASE
WHEN (available_physical_memory_kb * 1.0 / total_physical_memory_kb) < 0.10 THEN 'WARNING: Very low available memory'
WHEN (available_physical_memory_kb * 1.0 / total_physical_memory_kb) < 0.20 THEN 'CAUTION: Low available memory'
ELSE 'GOOD: Adequate available memory'
END AS Physical_Memory_Status,
CEILING(((total_page_file_kb * 1.00) / (total_physical_memory_kb * 1.00)) * 100) AS [Page_File_To_RAM_Ratio_Percentage],
CASE
WHEN ((total_page_file_kb * 1.00) / (total_physical_memory_kb * 1.00)) < 1 THEN 'WARNING: Page file smaller than RAM'
WHEN ((total_page_file_kb * 1.00) / (total_physical_memory_kb * 1.00)) >= 1.5 THEN 'GOOD: Optimal ratio'
ELSE 'OK: Acceptable ratio'
END AS Ratio_Status,
system_memory_state_desc AS System_Memory_State,
CASE system_memory_state_desc
WHEN 'Available physical memory is high' THEN 'GOOD: System memory state optimal'
WHEN 'Physical memory state is transitioning' THEN 'CAUTION: Memory state is changing'
ELSE 'WARNING: Memory pressure detected'
END AS Memory_State_Status
FROM sys.dm_os_sys_memory;
This will then return the values with a description of those values for easy reading, the main value to pay attention to is the "memory_state_status" which here is Good:
