List processes by working set with Get-Process | Sort-Object WorkingSet64 -Descending, and check available memory with the \Memory\Available MBytes counter. On database servers, SQL Server taking most of the RAM is by design unless max server memory is capped. A process whose memory grows steadily without levelling off is likely leaking.
Symptoms
- Task Manager shows memory at 90% or more
- Server is slow and the disk is busy paging
- Event log shows resource exhaustion warnings (event 2004)
How to diagnose it
Largest processes
Working set is the RAM a process is currently using.
Get-Process | Sort-Object WorkingSet64 -Descending | Select-Object -First 10 Name, Id, @{n='MB';e={[math]::Round($_.WorkingSet64/1MB)}}Available memory and paging
Low available MB plus high pages/sec means pressure.
Get-Counter '\Memory\Available MBytes','\Memory\Pages/sec' -SampleInterval 2 -MaxSamples 5Check for resource exhaustion events
The Resource-Exhaustion-Detector names the processes using the most memory.
Get-WinEvent -FilterHashtable @{LogName='System'; Id=2004} -MaxEvents 5 | Format-List TimeCreated, Message
Common causes and fixes
SQL Server using all available memory
Set max server memory, leaving several GB for Windows and other services.
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 12288; RECONFIGURE;Memory leak in an application or IIS pool
Recycle the app pool on a schedule or memory limit as a stopgap, and fix the leak.
Too many RDP sessions or user applications
Log off disconnected sessions.
query user
logoff SESSIONIDPrevent it happening again
- Alert when MEM_used is above 90% for 5 minutes; the Windows agent reports memory and top processes.
- Cap SQL Server and other caches explicitly on shared servers.
Set it up: Windows Server monitoring.
FAQ
Why does SQL Server use so much memory?
It caches data in RAM and keeps growing until it reaches max server memory. That is expected; cap it on servers running other workloads.
What is a working set?
The physical memory a process is currently using, as shown in Task Manager’s memory column.
Related guides
Get alerted before this becomes an outage.
Install the agent in one line. Statance watches CPU, memory, disk and services, and its AI assistant helps find the cause.