How I diagnosed SQL Server memory pressure in production
A production SQL Server started consuming RAM faster than expected — within a day of a fresh deployment, it was approaching 100% memory usage. This is the full investigation: what we found, what we fixed, and the monitoring stack we built after.
A production SQL Server started consuming RAM faster than expected — within a day of a fresh deployment, it was approaching 100% memory usage. No crashes, no alerts, just slow queries and a server quietly suffocating. This is the full investigation: what we found, what we fixed, and the monitoring stack we built to make sure it never blindsides us again.
The setup
The platform is a multi-tenant .NET eCommerce system running on Ubuntu. SQL Server runs inside a Docker container. At the time of the incident, we had no monitoring in place — that came later. The .NET application itself is installed directly on the host. Every new release involves a service restart — and that's where the pattern started.
The symptom
The day we deployed a new release and restarted both the .NET service and the SQL Server container, everything looked fine. By the next day, RAM was near 100%. SQL Server was under enough memory pressure that it had started paging to disk — reading and writing from storage instead of memory, which is orders of magnitude slower.
The system didn't go down. But it was clearly struggling.
Note: SQL Server's default behavior is to claim as much RAM as the OS allows and hold it. Under normal conditions this is fine — it's caching data for performance. Under our conditions, it was masking a deeper problem.
Step 1: pinpointing SQL Server as the source
First question: is this actually SQL Server consuming the RAM, or something else? We identified SQL Server as the culprit by checking process-level memory usage on the host. Once we confirmed the container was responsible, we needed to understand why SQL Server itself was consuming so much — which meant going inside.
Step 2: discovering DMVs and the plan cache
SQL Server exposes internal runtime data through Dynamic Management Views (DMVs) — system views that let you query things like active queries, memory usage, index health, and the plan cache. After some research, we found the view that mattered most for our situation:
SELECT TOP 20
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu,
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset) / 2) + 1) AS query_text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.execution_count ASC;
We sorted by execution_count ASC — plans executed only once. In a healthy system, frequently-run queries should have reusable cached plans. Seeing hundreds of single-use plans is a red flag: it means SQL Server is generating a new execution plan for every single request and caching all of them, bloating the plan cache indefinitely.
That's exactly what we found.
Step 3: finding the offending query
Inspecting the query text of those single-use plans, we found a query filtering records by date. In the .NET code, the date was being passed like this:
// The problem — value embedded directly in the query
var results = dbContext.Orders
.Where(o => o.CreatedAt > DateTime.Now.AddDays(-30))
.ToList();
DateTime.Now changes every second. Because the value was embedded directly into the query string rather than passed as a parameter, SQL Server treated each version as a completely different query — and generated and cached a brand new execution plan for each one. Every second, a new plan. Every plan taking up memory. Never reused, never evicted fast enough.
This is called plan cache pollution — a well-known SQL Server problem caused by non-parameterized queries. The fix is straightforward: parameterize everything.
Step 4: the fix
We refactored the query to use a proper parameter:
// The fix — value passed as a parameter
var cutoff = DateTime.Now.AddDays(-30);
var results = dbContext.Orders
.Where(o => o.CreatedAt > cutoff)
.ToList();
One line change. Now SQL Server compiles one plan for this query shape and reuses it on every call, regardless of what cutoff equals. After deploying the fix, we audited all other LINQ queries across the codebase for the same pattern and corrected them.
Step 5: adding a hard memory ceiling
Even with the query fixed, we wanted a safety net. SQL Server has a max server memory configuration option — but we discovered it doesn't behave correctly inside a Docker container. The fix was to apply the limit at the Docker level instead:
# docker-compose.yml
services:
sqlserver:
image: mcr.microsoft.com/mssql/server
mem_limit: 3g
cpus: '2.0'
This guarantees SQL Server can never consume more than 3 GB of host RAM, regardless of query behavior. Combined with the parameterization fix, this gave us two layers of protection: don't generate junk plans, and cap total memory even if something slips through.
Step 6: building visibility so this never surprises us again
The hardest part of this incident was that we had no visibility. The RAM was climbing and we didn't know until it was already a problem. After the fix, we set up a full observability stack:
Prometheus for metrics collection, Grafana for dashboards, and the prometheus-mssql-exporter running as a Docker container — it connects to SQL Server, reads DMV data, and exposes it as Prometheus metrics. We now track plan cache size, buffer pool usage, CPU, and overall RAM in real time.
Today, total server RAM usage sits around 60% under normal load — well within safe limits. Deployments happen roughly every 15–30 days and include a container restart. We haven't seen RAM pressure since.
Results
RAM exhaustion cycle — before
~1 day
RAM exhaustion cycle — after
1 month+
Server RAM usage — before
~100%
Server RAM usage — after
~60%
Key takeaways
Parameterize every query, always. Even when LINQ abstracts it, check what SQL is actually being generated — especially when filtering by dynamic values like dates or user input.
DMVs are your first debugging tool inside SQL Server. sys.dm_exec_query_stats and sys.dm_exec_query_plan give you direct visibility into what the engine is doing — no extra tooling needed.
Apply resource limits at the container level. max server memory doesn't behave inside Docker. Use mem_limit in Docker Compose instead.
Build observability before you need it. We set up Prometheus and Grafana after the incident. Ideally it should be there from day one.