SQL Server Memory Pressure Troubleshooting: How to Find the Cause and Fix It

Introduction

Suppose it’s 11:30 AM, and users start complaining that the application has become unusually slow. Some queries are timing out, reports are taking longer than usual, and the application team suspects SQL Server.

You connect to the server and notice that almost all the RAM is being used.

Your first thought might be:

“SQL Server needs more memory.”

But is that really the problem?

SQL Server is designed to use available memory to cache data pages and reduce disk I/O. Seeing high memory utilization doesn’t automatically mean the server is under memory pressure. The real problem is whether SQL Server or the operating system is struggling to obtain the memory it needs.

In this article, we’ll follow a practical DBA troubleshooting process to identify memory pressure, understand the evidence, choose the right fix, and validate the result.

1. What Is SQL Server Memory Pressure?

Memory pressure occurs when SQL Server or the operating system cannot obtain enough memory to meet current workload requirements efficiently.

It generally falls into three categories.

Type	What it means	Example
External memory pressure	Windows or another process needs memory	Antivirus or another application consumes available RAM
Internal SQL Server memory pressure	A SQL Server component needs more memory	The buffer pool, plan cache, or lock manager consumes substantial memory
Query execution memory pressure	Queries cannot obtain the memory grants they need	Several large sorts or hash joins compete for execution memory

These situations can produce similar symptoms, but their solutions are different. Increasing max server memory may help in one situation and make another worse.

The first rule: identify where the pressure originates before changing the configuration.

2. Recognize the Symptoms

Common signs of memory pressure include:

• Queries becoming slower during peak workload.
• Queries waiting on RESOURCE_SEMAPHORE.
• Query compilation delays associated with RESOURCE_SEMAPHORE_QUERY_COMPILE.
• Errors such as SQL Server error 701, indicating insufficient system memory.
• The SQL Server process being paged out by Windows.
• Performance deteriorating when several memory-intensive queries run concurrently.

Microsoft recommends investigating both operating-system memory availability and SQL Server’s internal memory usage when these symptoms occur.

Important: None of these symptoms alone proves the root cause. Collect evidence while the problem is occurring whenever possible.

3. Step One: Check the Operating System First

Before investigating SQL Server internals, check whether Windows itself is running low on memory.

Open Windows Performance Monitor (PerfMon) and collect these counters:

Counter	What to look for
Memory\Available MBytes	Whether available physical memory is consistently low
Process(sqlservr)\Working Set	Physical memory currently in the SQL Server process’s working set
Process(sqlservr)\Private Bytes	Private committed virtual memory used by the process
SQLServer:Memory Manager\Total Server Memory (KB)	Memory currently committed by the SQL Server memory manager
SQLServer:Memory Manager\Target Server Memory (KB)	Memory target SQL Server is trying to reach

These counters provide different perspectives on memory usage. Collect them over time rather than relying on one snapshot.

How should you interpret the results?

Scenario A: Windows has very little available memory.

Other applications, antivirus software, backup agents, or multiple SQL Server instances may be competing for RAM.

Next action: Identify the largest memory consumers and coordinate with the infrastructure team before changing SQL Server settings.

Scenario B: Windows has sufficient available memory, but queries are waiting for memory grants.

This points toward investigating SQL Server workload and query execution memory.

Next action: Check waiting queries and memory grants in Step 4.

Scenario C: SQL Server process memory is much higher than Total Server Memory.

Some allocations may be occurring outside the SQL Server memory manager’s usual accounting, such as allocations from linked-server providers, SQLCLR, or other in-process components.

Next action: Investigate process memory and SQL Server memory clerks together. A difference is a clue, not proof of a leak.

4. Step Two: Check SQL Server’s Memory Configuration

Now check how much memory SQL Server is allowed to use.

Run this query:

EXEC sys.sp_configure 'show advanced options';
GO

EXEC sys.sp_configure 'min server memory (MB)';
GO

EXEC sys.sp_configure 'max server memory (MB)';
GO

Suppose the server has 64 GB RAM and max server memory is set to 60,000 MB.

Is that wrong?

Not necessarily. But if the server also runs backup software, monitoring agents, or other applications, that configuration may leave insufficient headroom.

Conversely, if max server memory is set too low, SQL Server may be unable to retain enough useful data in memory for the workload.

Microsoft recommends configuring an appropriate upper memory limit and accounting for the operating system and other memory consumers. There is no single percentage that fits every server.

Example: Estimate an initial memory limit

Suppose:

Item	Memory
Total physical RAM	64 GB
Initial allowance for OS and other applications	12 GB
Remaining memory	52 GB

An initial max server memory setting might therefore be around 52 GB, subject to workload and other SQL Server memory requirements.

This is only an example, not a universal sizing formula. Monitor actual consumption and adjust based on evidence.

Change the setting only after reviewing the impact

For example, to set the limit to 52 GB:

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sys.sp_configure 'max server memory (MB)', 53248;
GO
RECONFIGURE;
GO

Before executing: Verify the server’s physical memory, other applications, existing settings, and any additional SQL Server instances. Record the original value and have a rollback plan.

Changing max server memory does not normally require a SQL Server service restart, but the impact of a change should still be monitored.

5. Step Three: Identify Which SQL Server Component Is Consuming Memory

SQL Server uses memory for many purposes: caching data pages, storing execution plans, managing locks, and executing queries.

To find out which components consume the most memory, query sys.dm_os_memory_clerks.

A memory clerk tracks allocations associated with a SQL Server component.

Run:

SELECT TOP (15)
    type,
    name,
    pages_kb / 1024.0 AS pages_mb,
    virtual_memory_committed_kb / 1024.0
        AS virtual_memory_committed_mb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

This gives you a starting point for investigating internal memory usage. It is not a complete accounting of every allocation in the SQL Server process.

How should you interpret the output?

Pay attention to the largest consumers and how their values change during the incident.

Memory clerk or component	What to investigate
MEMORYCLERK_SQLBUFFERPOOL	Buffer pool and data-page caching
CACHESTORE_SQLCP	Ad hoc and prepared SQL plan cache
CACHESTORE_OBJCP	Object plans, including stored procedures
MEMORYCLERK_SQLQERESERVATIONS	Query execution memory reservations
OBJECTSTORE_LOCK_MANAGER	Memory used by the lock manager

The names above are examples of useful investigation targets, not a list of components that should always be reduced.

Practical DBA tip: A large memory clerk is not automatically a problem. Look for unusual growth, pressure symptoms, and a connection to the affected workload before taking action.

6. Step Four: Investigate Queries Waiting for Memory

This is one of the most important steps when queries become slow during peak workload.

SQL Server allocates execution memory to operations such as sorting and hash joins. These allocations are called memory grants.

Imagine several reports running simultaneously. Each report needs substantial memory to sort or aggregate millions of rows. If SQL Server cannot grant the requested memory, some queries must wait.

A common wait type in this situation is RESOURCE_SEMAPHORE.

Run this query to identify requests waiting for a memory grant:

SELECT
    session_id,
    request_time,
    grant_time,
    requested_memory_kb / 1024.0 AS requested_mb,
    granted_memory_kb / 1024.0 AS granted_mb,
    required_memory_kb / 1024.0 AS required_mb,
    wait_time_ms,
    dop
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL
ORDER BY request_time;

What does the output tell you?

• grant_time IS NULL: The request has not yet received its memory grant.
• requested_mb: The requested amount of memory.
• granted_mb: The amount granted; it is typically NULL for requests still waiting.
• wait_time_ms: How long the request has been waiting for a grant.

If this query returns rows during the performance incident, investigate the waiting queries and the overall memory available for execution.

If it returns no rows, that does not rule out a memory problem. The pressure may have passed, or the problem may be elsewhere.

Find the SQL text for waiting requests

SELECT
    mg.session_id,
    mg.wait_time_ms,
    mg.requested_memory_kb / 1024.0 AS requested_mb,
    mg.granted_memory_kb / 1024.0 AS granted_mb,
    st.text AS sql_text
FROM sys.dm_exec_query_memory_grants AS mg
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
WHERE mg.grant_time IS NULL
ORDER BY mg.wait_time_ms DESC;

Review the SQL text, execution plan, estimated row counts, sorts, hash operations, and concurrency.

Do not immediately kill every waiting query. Determine which workload is business-critical and whether a specific query is consuming excessive resources.

7. Step Five: Find Queries Asking for Too Much Memory

Sometimes the server has enough total memory, but one or more queries request much more execution memory than they actually need.

Consider a report that joins several large tables and sorts the result.

Potential causes include:

• Poor cardinality estimates.
• Unnecessary sorting or aggregation.
• Excessive intermediate rows.
• Missing or unsuitable indexes.
• Inefficient join logic.
• Incorrect assumptions about parameter values.

Open the actual execution plan and inspect the memory grant information.

Look at:

• Requested memory.
• Granted memory.
• Maximum memory used.
• Sort or hash spills to TempDB.
• Estimated versus actual rows.

A large difference between granted and used memory may indicate over-granting. A query that needs more memory than it receives may spill intermediate results to TempDB.

Neither situation should be diagnosed from a single number alone.

What should you do?

1. Identify the expensive query.
2. Review its execution plan.
3. Check row estimates and data distribution.
4. Reduce unnecessary rows and operations.
5. Review indexes and statistics.
6. Test the change under a representative workload.

Query tuning can reduce memory requirements without buying additional RAM.

8. Step Six: Check for Memory-Related Errors

Check the SQL Server error log around the time the issue occurred.

Examples include:

Error	Why investigate it?
701	SQL Server cannot allocate sufficient system memory
802	Memory allocation for buffer-pool pages failed
8645	A query could not obtain the required execution memory grant in time
17890	SQL Server reports that memory was paged out by the operating system

These errors have different causes, so use the error details and surrounding events to guide the investigation.

You can also run:

DBCC MEMORYSTATUS;

This returns a detailed snapshot of SQL Server memory usage.

Use it when you need deeper diagnostics or when Microsoft Support asks for it. The output is extensive, so compare relevant sections across captures and focus on the memory manager, process/system counts, and large memory consumers.

9. Step Seven: Apply the Fix Based on the Evidence

Once you have identified the likely cause, choose the appropriate response.

Finding	Recommended next action
Other applications are consuming available RAM	Coordinate to reduce or move their memory usage
max server memory is too high	Set a suitable limit that leaves sufficient headroom
max server memory is too low	Evaluate whether it can safely be increased
Queries are waiting on RESOURCE_SEMAPHORE	Investigate memory grants, query plans, and concurrency
Large query memory grants or spills	Tune the query, estimates, indexes, or workload
Lock manager consumes unusually high memory	Investigate excessive locks and long-running transactions
Plan cache is growing due to many one-off plans	Investigate query parameterization and plan reuse
SQL Server process memory exceeds engine-accounted memory substantially	Investigate external allocations inside the process
The workload genuinely exceeds available capacity	Consider additional RAM or workload redistribution

Microsoft also recommends reviewing Resource Governor limits where Resource Governor is in use.

What about Lock Pages in Memory?

Lock Pages in Memory (LPIM) is sometimes suggested as a universal memory fix.

It isn’t.

LPIM may help when the operating system is trimming SQL Server’s working set, but it should be considered only after confirming the problem. It must be paired with an appropriate max server memory configuration so Windows and other processes retain sufficient memory.

10. Validate the Fix

Don’t close the incident just because the application appears faster.

Compare measurements from before and after the change under a comparable workload.

Metric	Before	After
Available OS memory	Consistently low	Adequate headroom
Memory grant waits	Frequent	Reduced or resolved
Query duration	12 seconds	3 seconds
TempDB spills	Repeated	Reduced
Memory-related errors	Repeated	No recurrence during validation

These are illustrative values, not benchmark results.

Continue monitoring during the next peak workload. A fix that works when five users are active may not work when 200 users run reports concurrently.

Permissions You Need

For on-premises SQL Server, many server-level memory DMVs require elevated monitoring permissions.

• SQL Server 2019 and earlier: VIEW SERVER STATE is commonly required for the DMVs covered here.
• SQL Server 2022 and later: Several of these DMVs require VIEW SERVER PERFORMANCE STATE instead. Verify the permission for each DMV you use.

Operating-system PerfMon counters require suitable Windows permissions. Changing server configuration requires appropriate SQL Server administrative permissions.

Use the least privilege your environment supports rather than granting sysadmin simply to investigate memory.

Common Mistakes to Avoid

1. Assuming high RAM usage means memory pressure. SQL Server uses RAM to cache data; high utilization can be normal.
2. Increasing max server memory without checking Windows. This may make external memory pressure worse.
3. Restarting SQL Server as the first response. A restart clears caches and disrupts active connections; it can hide evidence without fixing the cause.
4. Running DBCC FREEPROCCACHE or other cache-clearing commands casually. Clearing caches can cause recompilation and performance degradation.
5. Blaming the query that failed. The query may simply be the one that encountered a shortage caused by other workloads.
6. Changing several settings simultaneously. It becomes difficult to determine which change helped or caused a regression.

SQL Server Memory Pressure Troubleshooting Checklist

• Confirm when the slowdown started.
• Check available Windows memory.
• Review SQL Server process memory.
• Compare Total Server Memory with Target Server Memory.
• Review min server memory and max server memory.
• Check the SQL Server error log.
• Inspect sys.dm_os_memory_clerks.
• Check for waiting memory grants.
• Investigate RESOURCE_SEMAPHORE waits.
• Review expensive queries and actual execution plans.
• Check for spills to TempDB.
• Identify external memory consumers.
• Apply one evidence-based change at a time.
• Compare before-and-after measurements.
• Monitor the next peak workload.

Summary

SQL Server memory pressure is not solved simply by adding RAM or changing a configuration value.

A DBA must first determine whether the pressure comes from Windows, SQL Server components, or query execution memory. Then the fix should target the actual cause.

Don’t guess. Collect evidence, identify the root cause, fix it, and validate the result.

That is how memory troubleshooting becomes a repeatable production process rather than trial and error.