Find Memory Pressure in SQL Server

select * from sysprocesses

--if wait type is RESOURCE_SEMAPHORE, it is memory pressure.

SELECT * FROM sys.dm_exec_query_resource_semaphore

--find Query having memory contention.

select sp.*, st.text from sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st

WHERE sp.lastwaittype LIKE 'RESOURCE_SEMAPHORE_QUERY_COMPILE%' ORDER BY sp.waittime DESC;

---Find all queries waiting in the memory queue:

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

--Memory Pressure

DBCC memorystatus

--Check Small Gate, Medium Gate and Big Gate.