11 - Memory and the Buffer Pool

82 min read

Memory and the Buffer Pool


flowchart TD
    A["Memory concern"] --> B{"max server memory<br/>configured sanely?"}
    B --> Y1([YES])
    B --> N1([NO])
    N1 --> C["Set explicit max server memory<br/>before deeper tuning"]
    Y1 --> D{"OS or process memory<br/>shows pressure?"}
    D --> Y2([YES])
    D --> N2([NO])
    Y2 --> E["Check external pressure,<br/>host sizing, LPIM on Windows,<br/>and non-SQL memory consumers"]
    N2 --> F{"PLE low or dropping<br/>and buffer pool churn visible?"}
    F --> Y3([YES])
    F --> N3([NO])
    Y3 --> G["Find large scans,<br/>buffer pool skew by database,<br/>and poor page reuse"]
    N3 --> H{"Queries waiting on<br/>memory grants?"}
    H --> Y4([YES])
    H --> N4([NO])
    Y4 --> I["Inspect RESOURCE_SEMAPHORE,<br/>grant sizes, stats, indexes, and DOP"]
    N4 --> J{"Plan cache wasting memory?"}
    J --> Y5([YES])
    J --> N5([NO])
    Y5 --> K["Review ad hoc workload,<br/>single-use plans, and consider<br/>optimize for ad hoc workloads"]
    N5 --> L["Memory looks healthy;<br/>investigate waits, I/O, or query design"]

    classDef yesNode fill:#1f3b2d,stroke:#73d13d,stroke-width:2px,color:#c0caf5,font-weight:bold;
    classDef noNode fill:#4a1f24,stroke:#db4b4b,stroke-width:2px,color:#c0caf5,font-weight:bold;
    class Y1,Y2,Y3,Y4,Y5 yesNode;
    class N1,N2,N3,N4,N5 noNode;

Reproducible Baseline

Four-view memory baseline

SQL Server exposes four layered views that together answer “is memory healthy right now.” Capture them in order before drilling into any symptom:

  • sys.configurations — the tunable knobs: max server memory (MB), min server memory (MB), optimize for ad hoc workloads, min memory per query (KB), index create memory (KB), query wait (s).
  • sys.dm_os_sys_memory — OS-visible physical memory and the Resource Monitor state description.
  • sys.dm_os_process_memory — the sqlservr process view: physical memory in use, locked pages, page faults, and process-level low-memory flags.
  • sys.dm_os_sys_info — SQL Server’s internal target vs committed memory, plus the active memory model (CONVENTIONAL, LOCK_PAGES, or LARGE_PAGES).

Run all four before changing anything: a symptom at any one layer is always interpreted against the other three.

SQL Server | sys.configurations | audit memory-related settings

Every SQL Server memory investigation starts at sys.configurations, which exposes the server-level tunables that govern total memory, buffer pool size, plan-cache hygiene, grant sizing, and the query wait timeout. The goal at this layer is not to change anything yet — it is to confirm what the current tunable surface looks like, so every downstream observation can be interpreted against the right baseline.

At the start of any memory investigation, during initial host validation, and after any sp_configure change that touches a memory tunable. It is typically triggered by first configuration audit, post-install validation, post-restart verification, or a user complaint about query memory grants timing out. Read-only T-SQL against sys.configurations. Runs from any session with default permissions. No restart required; safe to run at any time. Produce a single baseline row per memory-related setting so that later “is this explained by configuration?” questions have an authoritative answer.

Cast the sql_variant columns to bigint and filter on the six memory-related configuration names.

SELECT
    name,
    CAST(value AS bigint) AS value,
    CAST(value_in_use AS bigint) AS value_in_use,
    CAST(minimum AS bigint) AS minimum,
    CAST(maximum AS bigint) AS maximum,
    is_dynamic,
    is_advanced
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'min server memory (MB)',
    'optimize for ad hoc workloads',
    'min memory per query (KB)',
    'index create memory (KB)',
    'query wait (s)'
)
ORDER BY name;
namevaluevalue_in_useminimummaximumis_dynamicis_advanced
index create memory (KB)007042147483647TrueTrue
max server memory (MB)214748364721474836471282147483647TrueTrue
min memory per query (KB)102410245122147483647TrueTrue
min server memory (MB)01602147483647TrueTrue
optimize for ad hoc workloads0001TrueTrue
query wait (s)-1-1-12147483647TrueTrue

Three of these rows are the load-bearing signals. First, max server memory (MB) = 2147483647 means this instance has no effective internal cap — it would let SQL Server grow until either the Linux memory.memorylimitmb ceiling or OS back-pressure intervenes. On a host that only exposes about 24.7 GB to SQL Server, leaving this at the default is not production-safe. Second, optimize for ad hoc workloads = 0 is not catastrophic on its own, but it becomes relevant below where single-use ad hoc plans occupy about 53.78 MB. Third, query wait (s) = -1 means memory-grant queueing uses the built-in formula (25× the query cost in seconds); setting it to a positive integer would override that with a hard wall-clock timeout, which is almost always worse under burst load.

SettingCurrent value_in_useWatchMeaningImplication
max server memory (MB)2147483647❌Default effectively-unlimited cap.SQL Server can consume whatever memory.memorylimitmb allows; the OS becomes the only back-pressure.
max server memory (MB)Explicit production value✅Buffer pool and most clerks are bounded intentionally.OS and non-SQL processes on the host are protected.
min server memory (MB)16 (system default on Linux)DependsSQL Server will not trim the buffer pool below this value.Usually irrelevant on single-instance Linux hosts; relevant only with contested hosts or multi-instance Windows.
optimize for ad hoc workloads0DependsFirst execution of an ad hoc statement stores a full compiled plan.Fine on parameterized workloads; wastes plan-cache memory on ad hoc-heavy systems.
optimize for ad hoc workloads1DependsFirst execution stores only a compiled plan stub.Usually beneficial when single-use ad hoc plans dominate the cache.
min memory per query (KB)1024 (1 MB, default)✅Lower bound for any sort or hash workspace grant.Rarely needs tuning; raise only on workloads with many tiny queries that spill.
index create memory (KB)0 (auto, default)✅Server decides the memory grant for CREATE INDEX sorts.Tune only when offline index builds routinely spill to tempdb.
query wait (s)-1 (formula, default)✅Memory-grant timeout is 25 * query_cost_seconds.Positive overrides almost always cause more timeouts, not fewer.

SQL Server | sys.dm_os_sys_memory | inspect OS-visible memory state

After the configuration audit, the next question is external: does the operating system currently see enough free physical memory for SQL Server to grow into? sys.dm_os_sys_memory answers that by exposing the host view — total RAM, available RAM, page file, system cache, and Resource Monitor’s own memory-state description. On Linux, the “page file” columns map to swap and the system-cache column is typically 0, but the physical memory and system_memory_state_desc columns are authoritative.

Inspect host RAM, available memory, and the memory state description

Any time the host might be under external memory pressure — user reports of SQL Server trimming, OOM-killer events, or a new workload being collocated on the host. It is typically triggered by an OOM incident, a suspected noisy-neighbor on the host, or routine health verification. Read-only T-SQL. Requires VIEW SERVER STATE (or VIEW SERVER PERFORMANCE STATE in SQL 2022+). Safe on production. Confirm whether the host is currently tight on physical memory so later symptoms can be blamed on (or cleared from) external pressure.

Convert the kilobyte columns to MB and return the Resource Monitor state description.

SELECT
    total_physical_memory_kb / 1024 AS total_ram_mb,
    available_physical_memory_kb / 1024 AS available_memory_mb,
    total_page_file_kb / 1024 AS total_page_file_mb,
    available_page_file_kb / 1024 AS available_page_file_mb,
    system_cache_kb / 1024 AS system_cache_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;
total_ram_mbavailable_memory_mbtotal_page_file_mbavailable_page_file_mbsystem_cache_mbsystem_memory_state_desc
247322204224732220420Available physical memory is high

This is a healthy external-memory snapshot. The instance sees about 24.7 GB of RAM, and about 22.0 GB remains available. The system_memory_state_desc is Available physical memory is high, which is Resource Monitor’s own conclusion that there is no host-level pressure right now. The system_cache_mb = 0 is expected on Linux: SQL Server uses direct I/O for database files and does not rely on the kernel’s page cache, so the Windows filesystem-cache column is always zero. The 24,732 MB total is also meaningful — /proc/meminfo inside the container reports about 30.9 GB, and SQL Server is showing 80% of that, which is the Linux default memory limit. That 80% default is why the instance shows 24.7 GB of “physical” memory even though the container sees 30.9 GB.

ColumnValue or PatternWatchMeaningImplication
system_memory_state_descAvailable physical memory is high✅OS memory pressure is low.SQL Server is not currently being squeezed by the host.
system_memory_state_descPhysical memory usage is steadyDependsStable state without a strong high/low signal.Monitor trends and pair with process-level signals.
system_memory_state_descAvailable physical memory is low❌Resource Monitor is flagging external pressure.SQL Server may trim memory or compete with the OS.
system_memory_state_descPhysical memory state is transitioningDependsPressure is changing direction.Retake the capture in a few minutes before concluding.
available_memory_mbHigh relative to host RAM✅Plenty of headroom remains.External memory pressure is unlikely right now.
available_memory_mbPersistently low❌The host is tight on free memory.Investigate host sizing, colocated processes, and SQL caps.
system_cache_mb0 on Linux✅Expected — SQL Server uses direct I/O.Do not treat the zero as a missing cache.

SQL Server | sys.dm_os_process_memory | inspect SQL Server process memory

sys.dm_os_process_memory is the sqlservr process view of its own memory. It exposes the values the OS would report for the process (physical memory in use, virtual address space reserved and committed, page-fault count) plus two internal flags — process_physical_memory_low and process_virtual_memory_low — that SQL Server uses to decide whether to trim caches. On Linux, the VAS numbers are enormous because the process has a 64-bit virtual address space, so the reserved/committed columns need to be read in proportion rather than as absolute quantities.

Inspect process memory and the two low-memory flags

After the OS view looks healthy but SQL Server is still behaving as if memory is constrained, or after a suspected leak inside a loaded assembly (CLR, Full-Text, MDS, PolyBase). It is typically triggered by unexpected cache trimming, low PLE with no obvious workload cause, or MEMORYCLERK_SQLCLR growth. Read-only T-SQL. Requires VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE. Determine whether the sqlservr process itself thinks it is under physical or virtual memory pressure, independent of what the host reports.

Select the process-memory columns, converting kilobyte columns to MB.

SELECT
    physical_memory_in_use_kb / 1024 AS physical_memory_in_use_mb,
    large_page_allocations_kb / 1024 AS large_page_allocations_mb,
    locked_page_allocations_kb / 1024 AS locked_page_allocations_mb,
    total_virtual_address_space_kb / 1024 AS total_vas_mb,
    virtual_address_space_reserved_kb / 1024 AS vas_reserved_mb,
    virtual_address_space_committed_kb / 1024 AS vas_committed_mb,
    virtual_address_space_available_kb / 1024 AS vas_available_mb,
    page_fault_count,
    memory_utilization_percentage,
    available_commit_limit_kb / 1024 AS available_commit_limit_mb,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;
physical_memory_in_use_mblarge_page_allocations_mblocked_page_allocations_mbtotal_vas_mbvas_reserved_mbvas_committed_mbvas_available_mbpage_fault_countmemory_utilization_percentageavailable_commit_limit_mbprocess_physical_memory_lowprocess_virtual_memory_low
42261300671088634096189467104767010022042FalseFalse

The process snapshot is clean. SQL Server is holding about 4.2 GB of physical memory, committed VAS is about 1.9 GB, and both low-memory flags are False. locked_page_allocations_mb = 0 is expected on Linux — LPIM is a Windows-only privilege model. The memory_utilization_percentage = 100 is also normal here: it means every page of committed memory is currently resident, which is the expected state on a system with plenty of free RAM and no swap pressure. The most notable detail is the gap between physical_memory_in_use_mb (4226) and vas_committed_mb (1894): the 2.3 GB difference is memory held by the process outside the regular commit accounting, typically SQLPAL, LibOS, CLR host, log pool, and other components loaded into the sqlservr process on Linux. That is a useful reminder that max server memory (MB) caps the buffer pool, not the total process footprint.

ColumnValue or PatternWatchMeaningImplication
process_physical_memory_lowFalse✅SQL Server does not consider process physical memory low.No immediate process-level pressure signal.
process_physical_memory_lowTrue❌SQL Server considers process physical memory low.Investigate host pressure, SQL growth, and memory cap immediately.
process_virtual_memory_lowFalse✅Virtual address space is not under pressure.Normal state on 64-bit.
process_virtual_memory_lowTrue❌VAS pressure exists.Memory allocation failures become more likely; extremely rare on 64-bit Linux.
locked_page_allocations_mb0 on Linux✅LPIM is Windows-only.Not a defect; ignore on Linux.
locked_page_allocations_mb0 on Windows❌ when LPIM is expectedLPIM privilege not granted or not effective.Grant Lock pages in memory to the service account and restart SQL Server.
memory_utilization_percentage100✅ on a non-swapping hostFull committed memory is resident.Expected state on a healthy Linux host.
memory_utilization_percentage< 100DependsSome committed memory is paged out.Investigate swap activity and the vmstat / si/so columns.
page_fault_count0 or low✅No obvious process-level fault activity.Consistent with a healthy snapshot.

SQL Server | sys.dm_os_sys_info | compare committed memory to target

The last baseline view, sys.dm_os_sys_info, is how SQL Server reports its own internal accounting: how much physical memory it currently holds (committed_kb), how much it would like to grow into under the current cap and workload (committed_target_kb), what memory model it is running (sql_memory_model_desc), and when the instance last restarted (sqlserver_start_time). The gap between committed and target is usually the best single internal-pressure indicator.

Compare committed versus target memory and confirm the memory model

Immediately after the sys.dm_os_process_memory capture, so internal and process views can be interpreted side by side. It is typically triggered by routine baseline, a suspected leak, or an “is SQL Server still growing?” question. Read-only T-SQL, VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Show whether SQL Server’s committed memory is still climbing toward its target (growth phase), has stabilised near the target (steady state), or is fighting the target (pressure).

Return committed vs target memory, the memory model, container type, and the instance start time.

SELECT
    physical_memory_kb / 1024 AS physical_memory_mb,
    committed_kb / 1024 AS committed_mb,
    committed_target_kb / 1024 AS target_mb,
    visible_target_kb / 1024 AS visible_target_mb,
    sql_memory_model,
    sql_memory_model_desc,
    container_type,
    sqlserver_start_time
FROM sys.dm_os_sys_info;
physical_memory_mbcommitted_mbtarget_mbvisible_target_mbsql_memory_modelsql_memory_model_desccontainer_typesqlserver_start_time
24732189422705227051CONVENTIONAL12026-04-11 15:55:55.537

SQL Server is currently running far below its target. It has committed about 1.9 GB but would be willing to grow toward about 22.7 GB under the current cap. That gap is entirely expected — the instance restarted at 2026-04-11 15:55:55.537 and has been running for roughly 40 minutes at capture time, so the buffer pool has not yet warmed up to steady state. The important operational issue remains the same as before the restart: with max server memory (MB) still at 2147483647, there is no internal cap, and the only real ceiling is the Linux default memory.memorylimitmb = 80% of /proc/meminfo. The sql_memory_model = 1 (CONVENTIONAL) confirms that neither LPIM nor large pages are active — normal on Linux. The container_type = 1 flag tells SQL Server it is cgroup-aware, which matters for how it computes physical_memory_mb.

ColumnValue or PatternWatchMeaningImplication
committed_mbMuch lower than target_mb after a restart✅SQL Server is still warming up toward its target.Not pressure; wait for steady state before judging.
committed_mbMuch lower than target_mb in steady stateDependsHeadroom remains.Internal pressure is unlikely right now.
committed_mbApproximately equal to target_mbDependsNear target under current cap.Normal on busy systems; pair with grants and PLE.
committed_mbPersistently fighting or tracking below target_mb under load❌SQL Server is trying to grow and something is blocking it.Investigate host pressure, cap sizing, or collocated processes.
sql_memory_model_descCONVENTIONALDependsStandard memory model.Normal on Linux; on Windows it means LPIM is not active.
sql_memory_model_descLOCK_PAGES✅ on tuned Windows serversLocked pages are active.Requires an explicit max server memory cap.
sql_memory_model_descLARGE_PAGESDependsLarge-page allocations are active.Specialist configuration; validate carefully.
container_type1✅ on Linux containerscgroup-aware memory accounting is active.SQL Server respects cgroup v2 limits from SQL 2022 CU20+.
sqlserver_start_timeRecentContextCounters since startup are short.Do not over-interpret PLE or hit ratio right after a restart.

Linux Host Memory Boundaries

Four memory ceilings on Linux

On Linux, SQL Server’s memory ceiling is not max server memory (MB) alone. It is the minimum of four layered boundaries, and misunderstanding the layering is the single most common cause of unexpected OOM or unexpected trimming in containerised deployments:

  • Host physical memory — what /proc/meminfo MemTotal reports inside the container’s mount namespace.
  • cgroup v2 memory.max — the kernel-enforced upper bound for the container. max means uncapped. SQL Server 2022 CU20+ and SQL Server 2025 honor this value directly; older builds ignored it.
  • memory.memorylimitmb (or MSSQL_MEMORY_LIMIT_MB env var) — the SQL-Server-specific cap for the entire sqlservr process, defaulting to 80% of the lesser of host RAM and cgroup. Limits total process memory (buffer pool + SQLPAL + LibOS + CLR + Full-Text + Agent + any other loaded component).
  • max server memory (MB) — the T-SQL cap for the buffer pool and most memory clerks only. Must be set below memory.memorylimitmb to leave room for non-buffer-pool components inside the same process.

Every Linux memory investigation has to place the running numbers on this ladder. An instance showing 4.2 GB in physical_memory_in_use_mb but 1.9 GB in committed_mb is only confusing until you remember that max server memory (MB) caps the lower number and memory.memorylimitmb caps the higher one.

Linux | /proc/meminfo | inspect host memory seen by the container

The container’s view of host memory is the ground truth everything else derives from. Inside the stoxx-db container, /proc/meminfo reports the Linux kernel’s accounting as it appears to the container’s mount namespace. When cgroup v2 is active and memory.max is unlimited, MemTotal reports the full host RAM, not the container’s share. SQL Server’s default memory.memorylimitmb = 80% is applied on top of whatever MemTotal reports.

Read /proc/meminfo from inside the stoxx container

During initial host validation, after any change to Docker’s --memory flag or a Kubernetes resources.limits.memory value, or when sys.dm_os_sys_memory reports a total_ram_mb that does not match the host you expected. It is typically triggered by unexpected total_ram_mb in sys.dm_os_sys_memory, a new container deployment, or suspected noisy-neighbor pressure. Linux shell via docker exec, runs as root inside the container. Read-only. Confirm the physical memory the container actually sees, independently of what SQL Server reports.

Read the first ten lines of /proc/meminfo from inside the running stoxx-db container.

docker exec stoxx-db bash -c "cat /proc/meminfo | head -10"
MemTotal:       31656120 kB
MemFree:        27588752 kB
MemAvailable:   27854336 kB
Buffers:            3744 kB
Cached:           509124 kB
SwapCached:            0 kB
Active:           186124 kB
Inactive:        3380772 kB
Active(anon):       2688 kB
Inactive(anon):  3056136 kB

The container sees about 30.9 GB (31656120 kB) of total memory. That is meaningfully larger than the 24,732 MB SQL Server reported in sys.dm_os_sys_info.physical_memory_mb. The difference is exactly the Linux default: 24732 / 30914 = 80%, which is the value memory.memorylimitmb uses when no explicit limit is set. The kernel’s own MemAvailable of about 27.2 GB is what the Linux OOM killer and Resource Monitor will use to make memory pressure decisions — it is not what SQL Server uses for its own planning, but it is what would kill the container if the kernel decided the whole host was out of memory.

CounterMeaningOperational use
MemTotalTotal physical memory seen by the container’s mount namespace.Baseline for SQL Server’s 80% default.
MemFreeMemory not allocated to any process or kernel cache.Usually small on a warm host; not an alarm signal by itself.
MemAvailableKernel estimate of what a new process could allocate without swapping.The number the Linux OOM killer effectively cares about.
BuffersBlock-device buffer cache.Usually tiny on SQL Server hosts because SQL uses direct I/O.
CachedKernel page cache.Usually small on SQL Server Linux because data files are opened O_DIRECT.

Linux | cgroup v2 | check container memory limits

Below /proc/meminfo, the next ceiling is the kernel’s cgroup accounting. On cgroup v2 (the default on modern Ubuntu and RHEL), memory.max is the hard ceiling the container cannot cross, and memory.current is the current usage. SQL Server 2022 CU20 and SQL Server 2025 detect this value and treat it as the effective host RAM; older builds read only /proc/meminfo, which is the root cause of the “container lies about RAM and SQL Server OOMs” class of incidents that the CU20 fix targets.

Read memory.max and memory.current from the cgroup v2 hierarchy

At container deployment, after a Docker/Kubernetes memory-limit change, or during an OOM post-mortem. It is typically triggered by OOM event, Kubernetes OOMKilled pod, or a mismatch between /proc/meminfo and sys.dm_os_sys_info.physical_memory_mb. Linux shell via docker exec. Read-only access to /sys/fs/cgroup/memory.*. Works on cgroup v2 only — use /sys/fs/cgroup/memory/memory.limit_in_bytes on cgroup v1 hosts. Confirm the kernel-enforced upper bound on container memory and the container’s current consumption.

Read memory.max (the hard cap) and memory.current (current usage) from the cgroup v2 filesystem.

docker exec stoxx-db bash -c "cat /sys/fs/cgroup/memory.max; cat /sys/fs/cgroup/memory.current"
max
2767495168

The first line is max, which means the container is running without a hard cgroup limit — Docker was not started with --memory, so the cgroup inherits the host’s unlimited ceiling. The second line, 2767495168, is bytes and converts to about 2.64 GB of memory currently held by the container. That matches the committed_mb = 1894 plus about 850 MB of non-committed process memory held by SQLPAL, CLR, and LibOS. The combination “no cgroup cap + host has plenty of free RAM” is why the OS view and the SQL Server view agree that there is no pressure right now.

FileFormatMeaning
memory.maxmax or positive integer in bytesUpper bound the cgroup can consume. max = inherited from parent.
memory.currentInteger in bytesCurrent total resident memory of all processes in the cgroup.
memory.highmax or integerThrottle threshold — the kernel starts reclaiming pages once memory.current crosses this, without OOM-killing.
memory.eventsKey-value textCumulative events: low, high, max, oom, oom_kill. The right place to confirm whether the container has been OOM-killed.

Linux | mssql-conf | memory.memorylimitmb setting

memory.memorylimitmb is the SQL-Server-specific cap on total process memory. It is stored in /var/opt/mssql/mssql.conf (or configured via the MSSQL_MEMORY_LIMIT_MB env var, which takes precedence). Unlike max server memory (MB), it caps the entire sqlservr process — buffer pool, SQLPAL, LibOS, SQL Server Agent, Full-Text Search, Machine Learning Services, and anything else loaded into the process. When it is not set explicitly, SQL Server uses 80% of the lesser of /proc/meminfo MemTotal and the cgroup v2 limit.

memory.memorylimitmb caps the whole process, not just the buffer pool

Leaving max server memory (MB) at its unlimited default and relying on memory.memorylimitmb is dangerous because a large query with a huge grant can still push total process memory past the limit, especially on SQL Server versions earlier than SQL 2022 CU14 / SQL 2019 CU27 where a known bug allowed resident set size to exceed memory.memorylimitmb.

Set max server memory (MB) below memory.memorylimitmb

On the stoxx host, with memory.memorylimitmb at the default (about 24,732 MB = 80% of 30.9 GB), max server memory (MB) should be set to roughly 22,000 MB. That leaves about 2.7 GB for SQLPAL, CLR, LibOS, Full-Text, Agent, and any other component loaded in the sqlservr process, plus a small margin for the OS kernel and other host processes.

Check the current mssql-conf memory configuration

During initial host validation, after a container rebuild, or when sys.dm_os_sys_info.physical_memory_mb does not match the expected host RAM. It is typically triggered by first host audit, suspected memory leak, post-upgrade drift check. Linux shell as root inside the container. Reads /var/opt/mssql/mssql.conf. Read-only. Confirm whether memory.memorylimitmb is at its default (80% of host) or has been set to an explicit value.

Query mssql-conf for the current memory configuration. An empty response means the default is in effect.

docker exec stoxx-db bash -c "/opt/mssql/bin/mssql-conf get memory"
No setting for the given option found in '/var/opt/mssql/mssql.conf'.

The stoxx instance has no explicit memory.* settings in mssql.conf, so every memory option is at its default. memory.memorylimitmb defaults to 80% of the lesser of host RAM and the cgroup limit, which is why sys.dm_os_sys_info.physical_memory_mb = 24732 even though /proc/meminfo reports about 30.9 GB. On a production Linux host, the operator would typically set an explicit value here so that future cgroup or host changes do not silently move the ceiling.

OptionDescriptionDefaultProduction guidance
memory.memorylimitmbTotal process memory cap in MB. Limits buffer pool + SQLPAL + LibOS + Agent + any loaded component.80% of the lesser of host RAM and cgroup limitSet explicitly to a value lower than the host RAM and cgroup limit, leaving headroom for the OS.
memory.disablememorypressureDisables SQL Server’s internal memory-pressure signals. Values: true or false (default).falseLeave at false. Disabling inhibits trimming and lets the process eventually exceed memorylimitmb.
memory.memory_optimizedEnables persistent-memory file enlightenment and memory protection. Values: true or false.falseEnable only when the underlying storage is persistent memory (PMEM/NVDIMM).
memory.enablecontainersharedmemoryEnables VDI backup/restore shared-memory channel inside containers.falseEnable only when using VDI backup tools inside containers.

Set an explicit mssql-conf memory.memorylimitmb (pattern)

During controlled host provisioning — a planned maintenance window with exclusive access to the SQL Server service. It is typically triggered by initial host setup, a change to the cgroup or host memory, or remediation after an OOM incident. Linux shell as root. Modifies /var/opt/mssql/mssql.conf. Requires a systemctl restart mssql-server (or docker restart for containers) to take effect. This pattern is not executed against stoxx in this note — it is shown as a remediation reference. Bound total SQL Server process memory at a value that leaves explicit headroom for the OS and other host processes.

Set memory.memorylimitmb to 22000 inside the stoxx-db container and restart the SQL Server service. This is a remediation pattern and is not executed against the live stoxx instance.

docker exec -u root stoxx-db /opt/mssql/bin/mssql-conf set memory.memorylimitmb 22000
docker restart stoxx-db

Buffer Pool Health

Three buffer pool signals

Once the reproducible baseline and the Linux host boundaries are cleared, the next layer is the buffer pool itself. Three signals together describe whether cached pages are turning over quickly, being served efficiently, and distributed sensibly across databases:

  • Page Life Expectancy (PLE) per buffer node, from sys.dm_os_performance_counters. Measures how long a page stays cached before eviction.
  • Buffer cache hit ratio — correctly computed from the raw counter and its base, not from the raw fraction alone.
  • Buffer pool occupancy by database, from sys.dm_os_buffer_descriptors. Shows which databases are holding cached pages and how much of that is dirty versus clean.

On a freshly restarted instance, all three of these signals need time to stabilise. The values captured here are from an instance that started at 2026-04-11 15:55:55.537, so PLE is still climbing from zero and the hit ratio is still averaging over a small number of samples.

SQL Server | sys.dm_os_performance_counters | inspect Page Life Expectancy by buffer node

Page Life Expectancy is the number of seconds a newly cached page would live in the buffer pool before being evicted, assuming the current eviction rate holds steady. sys.dm_os_performance_counters exposes PLE at two granularities: the aggregate SQLServer:Buffer Manager counter, and one SQLServer:Buffer Node row per NUMA memory node. On multi-NUMA servers, the node-level numbers often diverge from the aggregate when one node takes the brunt of a scan-heavy workload, so it is worth reading both even on a single-node box like stoxx to establish the habit.

Retrieve PLE at the Buffer Manager and Buffer Node granularity

During any buffer pool health check, when a user complaint mentions slow reads, or when sys.dm_os_buffer_descriptors output shows an incidental database dominating cache. It is typically triggered by routine health check, post-restart warm-up verification, or investigation of read latency spikes. Read-only T-SQL. Requires VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE. Confirm how long cached pages are surviving in the buffer pool and whether the aggregate number hides NUMA-local skew.

Filter sys.dm_os_performance_counters on the PLE counter in any Buffer object, and return both the aggregate and per-node rows.

SELECT
    object_name,
    counter_name,
    instance_name,
    cntr_value AS ple_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer%'
ORDER BY object_name, instance_name;
object_namecounter_nameinstance_nameple_seconds
SQLServer:Buffer ManagerPage life expectancy2550
SQLServer:Buffer NodePage life expectancy0002550

PLE is about 2,550 seconds (42.5 minutes). That is neither high nor alarming in context: the instance restarted at 2026-04-11 15:55:55.537 and has been running for roughly the same duration at capture time, so PLE is effectively tracking the age of the oldest pages since startup rather than reaching a true steady-state ceiling. The aggregate and the node-level rows are identical because stoxx exposes a single buffer node — on multi-NUMA servers, a Buffer Node 000 value half the size of Buffer Node 001 would indicate uneven workload distribution, usually from a parallel plan that does not respect scheduler locality. Do not try to judge this particular capture against a fixed “PLE should be > 300” rule of thumb: post-restart PLE climbs linearly with time-since-start until the buffer pool is fully warmed, and the rule of thumb only applies in steady state.

ColumnValue or RangeWatchMeaningImplication
ple_secondsRising after a restart, tracking wall-clock✅Buffer pool is warming up.Expected; retake the capture after steady state.
ple_secondsVery high and stable in steady state✅Cached pages live a long time before eviction.Buffer pool churn is low.
ple_secondsRepeatedly collapsing or oscillating❌Pages are being evicted quickly and reloaded.Investigate scans, poor reuse, or true memory shortage.
object_name = SQLServer:Buffer NodePresent✅Node-level PLE is available.Use it to detect localized NUMA pressure.
instance_name differing sharply across nodesPresent on multi-node hosts❌ if skewedOne node is under heavier pressure than others.Investigate scheduler locality and query distribution.

SQL Server | sys.dm_os_performance_counters | calculate buffer cache hit ratio correctly

The buffer cache hit ratio is published as a Perfmon fraction counter, meaning it has two rows in sys.dm_os_performance_counters: the raw numerator (Buffer cache hit ratio) and its denominator (Buffer cache hit ratio base). Reading the raw value alone gives a meaningless number that happens to look like a percentage but is not one. The correct computation is 100.0 * raw / base, protected with NULLIF against a zero base.

Compute the buffer cache hit ratio from its numerator and base

As a secondary buffer-pool signal, alongside PLE, during any memory or I/O investigation. It is typically triggered by user complaint about read latency, suspected cache churn, or routine health check. Read-only T-SQL, VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. The counter is cumulative since SQL Server startup. Produce the actual buffer cache hit ratio as a percentage, not the raw fraction counter that Perfmon publishes.

Pivot the two counter rows into a single row with the computed percentage.

WITH counters AS
(
    SELECT
        counter_name,
        cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base')
      AND object_name LIKE '%Buffer Manager%'
)
SELECT
    CAST
    (
        100.0
        * MAX(CASE WHEN counter_name = 'Buffer cache hit ratio' THEN cntr_value END)
        / NULLIF(MAX(CASE WHEN counter_name = 'Buffer cache hit ratio base' THEN cntr_value END), 0)
        AS decimal(10,2)
    ) AS buffer_cache_hit_ratio_pct,
    MAX(CASE WHEN counter_name = 'Buffer cache hit ratio' THEN cntr_value END) AS hit_ratio_raw,
    MAX(CASE WHEN counter_name = 'Buffer cache hit ratio base' THEN cntr_value END) AS hit_ratio_base
FROM counters;
buffer_cache_hit_ratio_pcthit_ratio_rawhit_ratio_base
100.00239239

The corrected ratio is 100.00%. Both the raw and base values are 239, which is exactly why the raw counter alone is not usable — it looks like 239 and would mislead an untrained reader into reporting a “239% hit ratio” or a “239 cache hit rate”. The only correct reading is the ratio of the two. Even when calculated correctly, this metric is cumulative since startup, so it smooths out bursts and short-duration cache pressure. PLE and the wait patterns in sys.dm_os_wait_stats remain better short-term pressure indicators; the hit ratio is a long-run correlation check, not a real-time alert.

ColumnValue or RangeWatchMeaningImplication
buffer_cache_hit_ratio_pct>= 99✅Almost all page requests are served from cache.Good long-run cache effectiveness.
buffer_cache_hit_ratio_pct95 - 99DependsSome physical reads are occurring.Could still be fine; read with PLE and I/O waits.
buffer_cache_hit_ratio_pct< 95❌Cache misses are materially high.Investigate memory pressure, scans, and cache churn.
hit_ratio_raw without hit_ratio_basePresent❌ for interpretationRaw fraction numerator only.Never present it as the percentage by itself.

SQL Server | sys.dm_os_buffer_descriptors | buffer pool usage by database

Once PLE and the hit ratio confirm whether the buffer pool is healthy overall, the next question is compositional: which databases are holding the cached pages, and how much of that cache is dirty (modified and waiting to flush) versus clean (reusable or immediately evictable)? sys.dm_os_buffer_descriptors exposes one row per cached 8 KB page and is the authoritative answer. The DMV is expensive to scan on large buffer pools because the row count equals the total page count, so keep queries targeted and use COUNT_BIG / SUM aggregations rather than any per-page operation.

List the top ten databases by buffer pool footprint with dirty/clean breakdown

When PLE collapses without an obvious external cause, when an ETL or backup job is suspected of evicting production pages, or when the hit ratio drops. It is typically triggered by cache-churn investigation, post-incident review, or workload validation after a new pipeline is deployed. Read-only T-SQL against sys.dm_os_buffer_descriptors. Requires VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Expensive on large buffer pools — do not run it in a tight monitoring loop. Identify which databases currently own the buffer pool and whether an incidental database is dominating it.

Return the top ten databases by cached page count with a clean/dirty split and the percent of total buffer pool.

WITH bd AS
(
    SELECT
        database_id,
        COUNT_BIG(*) AS page_count,
        SUM(CAST(is_modified AS bigint)) AS dirty_page_count
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id <> 32767
    GROUP BY database_id
)
SELECT TOP (10)
    DB_NAME(database_id) AS database_name,
    CAST(page_count * 8.0 / 1024 AS decimal(18,2)) AS buffer_pool_mb,
    CAST(dirty_page_count * 8.0 / 1024 AS decimal(18,2)) AS dirty_pages_mb,
    CAST((page_count - dirty_page_count) * 8.0 / 1024 AS decimal(18,2)) AS clean_pages_mb,
    CAST(100.0 * page_count / NULLIF(SUM(page_count) OVER (), 0) AS decimal(10,2)) AS pct_of_cached_pages
FROM bd
ORDER BY page_count DESC;
database_namebuffer_pool_mbdirty_pages_mbclean_pages_mbpct_of_cached_pages
stoxx385.450.34385.1191.54
msdb8.440.388.052.00
stoxx_backup8.230.028.201.95
model_msdb4.940.334.611.17
model_replicatedmaster3.611.352.260.86
stoxx_db3.471.801.660.82
tempdb3.251.451.800.77
master2.850.172.680.68
model0.840.000.840.20

The buffer pool is heavily dominated by the primary workload database: stoxx alone holds about 91.54% of cached pages at 385.45 MB, with only 0.34 MB dirty. That dirty/clean ratio is the healthy shape — most cached pages are immediately evictable, and the small dirty footprint means checkpoints and lazy writes are keeping up with whatever writes are happening. tempdb is unusually low here at 3.25 MB, which is consistent with the post-restart state: tempdb is effectively unused until queries start generating sorts, hashes, and row versions. In steady state, expect tempdb to grow substantially as workspace memory grants spill and version-store pages accumulate. The presence of stoxx_backup and stoxx_db as noticeable occupants is a hint that those are actively queried sibling databases on this instance, not just dormant copies — a useful observation for operators who assume only the primary database matters.

ColumnValue or PatternWatchMeaningImplication
pct_of_cached_pagesHigh on the primary workload database✅Cache is aligned with the main workload.Usually expected.
pct_of_cached_pagesHigh on an incidental database❌Buffer pool is being consumed by lower-value activity.Investigate scans, ETL, logging tables, or missing indexes.
dirty_pages_mbPersistently high relative to cleanDependsMore modified pages are waiting to be flushed.Normal during write activity, but pair with I/O and checkpoint behavior.
dirty_pages_mbHigh on tempdb❌Version store or worktable churn.Indicates spill activity or long-running row-versioned transactions.
clean_pages_mbDominant in steady state✅Most cached pages are immediately reusable or evictable.Typical healthy state.

Memory Consumers

Five views of where memory is going

After the baseline, Linux boundaries, and buffer pool health, the next layer answers “where exactly is the committed memory going?” Five complementary views:

  • sys.dm_os_memory_clerks — the top-level categorisation (buffer pool, plan caches, lock manager, CLR, log pool, SOSNode, …).
  • sys.dm_exec_cached_plans — plan cache composition by object type (Adhoc, Proc, Prepared, View, Trigger, …).
  • sys.dm_exec_cached_plans with usecounts = 1 — single-use ad hoc plans, the canonical plan-cache-waste indicator.
  • sys.dm_os_memory_cache_counters — one row per cache store (CACHESTORE_SQLCP, CACHESTORE_OBJCP, USERSTORE_DBMETADATA, …), finer granularity than clerks.
  • DBCC MEMORYSTATUS — the canonical one-shot diagnostic dump, identical on Linux and Windows, used in almost every Microsoft Support incident that involves memory.

Clerks and cache counters are the hierarchical answer: clerks group cache stores by high-level category, and cache counters break each cache store out individually. DBCC MEMORYSTATUS is the cross-cutting fallback when one of the DMV queries is ambiguous or when you need a single diagnostic artifact to send to support.

SQL Server | sys.dm_os_memory_clerks | top memory clerks

Every allocation SQL Server makes goes through a memory clerk. The DMV exposes about 120 clerk types on a running instance, most of them holding trivial amounts. The interesting pattern is the top ten by pages_kb (the page-allocator memory), which together almost always account for the vast majority of committed memory. The three columns to read side by side are pages_mb, vm_reserved_mb, and vm_committed_mb: clerks like MEMORYCLERK_SQLCLR reserve huge virtual address space without committing most of it, and treating the reserved column as “real” memory overstates CLR usage by two orders of magnitude.

List the top ten memory clerks with page, VAS reserved, and VAS committed columns

Whenever the baseline shows committed memory growing unexpectedly, when sys.dm_os_sys_info.committed_mb approaches the target, or during any routine memory audit. It is typically triggered by suspected memory leak, post-incident review, or cache-bloat investigation. Read-only T-SQL. Requires VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Cheap to run. Identify which memory clerks hold most of the page-based memory, and confirm the ratio of reserved-to-committed VAS for each.

Return the top ten memory clerks by pages_mb with paired reserved and committed VAS columns.

WITH clerks AS
(
    SELECT
        type,
        name,
        SUM(pages_kb) / 1024.0 AS pages_mb,
        SUM(virtual_memory_reserved_kb) / 1024.0 AS vm_reserved_mb,
        SUM(virtual_memory_committed_kb) / 1024.0 AS vm_committed_mb
    FROM sys.dm_os_memory_clerks
    GROUP BY type, name
)
SELECT TOP (10)
    type,
    name,
    CAST(pages_mb AS decimal(18,2)) AS pages_mb,
    CAST(vm_reserved_mb AS decimal(18,2)) AS vm_reserved_mb,
    CAST(vm_committed_mb AS decimal(18,2)) AS vm_committed_mb,
    CAST(100.0 * pages_mb / NULLIF(SUM(pages_mb) OVER (), 0) AS decimal(10,2)) AS pct_of_clerk_pages
FROM clerks
ORDER BY pages_mb DESC;
typenamepages_mbvm_reserved_mbvm_committed_mbpct_of_clerk_pages
MEMORYCLERK_SQLBUFFERPOOLClient-Default444.48639.1653.3753.60
MEMORYCLERK_SOSNODESOS_Node71.010.000.008.56
CACHESTORE_SQLCPSQL Plans65.660.000.007.92
CACHESTORE_PHDRBound Trees50.230.000.006.06
MEMORYCLERK_SQLCLRClient-Default42.196154.978.195.09
MEMORYCLERK_SQLSTORENGClient-Default20.7820.5020.502.51
CACHESTORE_OBJCPObject Plans19.990.000.002.41
MEMORYCLERK_SQLGENERALClient-Default18.970.000.002.29
CACHESTORE_SYSTEMROWSETSystemRowsetStore13.090.000.001.58
MEMORYCLERK_SQLLOGPOOLLog Pool12.740.000.001.54

The buffer pool is correctly dominant at about 53.60% of clerk pages. That share is lower than the steady-state number you would expect on a fully warmed instance because the buffer pool is still filling in after the recent restart. Three rows deserve explicit attention. CACHESTORE_SQLCP at 65.66 MB (7.92%) is the ad hoc and prepared plan cache, and it is running larger than CACHESTORE_OBJCP (19.99 MB, 2.41%), which is the stored-procedure plan cache. The ratio CACHESTORE_SQLCP > CACHESTORE_OBJCP is the early-warning marker for ad hoc bloat, and it is consistent with the single-use ad hoc count captured further below. MEMORYCLERK_SQLCLR shows 6,154.97 MB reserved but only 8.19 MB committed, which is the canonical “read committed, not reserved” example: the CLR host reserves a large VAS region on startup so the hosted CLR can grow without fragmenting the address space, but almost none of it is backed by physical memory. Reading vm_reserved_mb alone would overstate CLR usage by a factor of 750. MEMORYCLERK_SQLLOGPOOL at 12.74 MB is the log pool, used to cache log records for crash recovery and change tracking — its presence in the top ten is normal on write-active instances.

ClerkWatchMeaningOperational implication
MEMORYCLERK_SQLBUFFERPOOL✅ when dominantMain cached data and index pages.Usually the largest clerk on a healthy disk-based workload.
MEMORYCLERK_SOSNODEDependsSQLOS internal scheduler and node memory.Proportional to configured scheduler count; usually steady.
CACHESTORE_SQLCP❌ if much larger than CACHESTORE_OBJCPAd hoc and prepared plan cache.High values point to ad hoc workload bloat.
CACHESTORE_OBJCPDependsStored procedure and module plans.Large values can still be normal on procedure-heavy systems.
CACHESTORE_PHDRDependsBound trees and compile-time structures.Usually smaller; unusual growth can reflect complex compilations.
MEMORYCLERK_SQLCLRDepends — read committed, not reservedCLR-related memory.Large reserved VAS is normal; large committed VAS is not.
MEMORYCLERK_SQLSTORENGDependsStorage engine internals (row-version store, DBCC structures).Grows with active transactions and row versioning.
MEMORYCLERK_SQLLOGPOOLDependsLog pool for log records.Grows with write activity.
OBJECTSTORE_LOCK_MANAGER❌ if unusually largeLock memory.Large values can indicate blocking, very high concurrency, or lock-heavy scans.
MEMORYCLERK_XTPDependsIn-Memory OLTP memory.Only present when memory-optimised tables exist.
MEMORYCLERK_SQLQERESERVATIONS❌ if largeQuery execution workspace reservations.Large values correlate directly with outstanding memory grants.

SQL Server | sys.dm_exec_cached_plans | plan cache composition by object type

sys.dm_exec_cached_plans is the drill-down below CACHESTORE_SQLCP and CACHESTORE_OBJCP. It returns one row per cached plan, with a size_in_bytes column and an objtype classification (Adhoc, Prepared, Proc, View, Trigger, Rule, Default, UsrTab, Check). Aggregating by objtype is the first pass that tells you whether plan cache waste is coming from ad hoc statements, from views, or from stored procedures.

Aggregate cached plans by object type with size and use counts

After CACHESTORE_SQLCP appears in the top clerks, or when the user reports spikes in compile time or plan-cache memory. It is typically triggered by plan-cache bloat suspicion, a workload migration that changed query patterns, or a regular hygiene check. Read-only T-SQL, VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Cheap. Produce a one-row-per-object-type summary of plan cache occupancy and reuse.

Group cached plans by objtype with memory, total use count, and average reuse.

WITH plans AS
(
    SELECT
        objtype AS plan_type,
        COUNT(*) AS plan_count,
        SUM(size_in_bytes) / 1048576.0 AS cache_mb,
        SUM(usecounts) AS total_use_count,
        AVG(CONVERT(float, usecounts)) AS avg_use_count
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
)
SELECT
    plan_type,
    plan_count,
    CAST(cache_mb AS decimal(18,2)) AS cache_mb,
    total_use_count,
    CAST(avg_use_count AS decimal(18,2)) AS avg_use_count,
    CAST(100.0 * cache_mb / NULLIF(SUM(cache_mb) OVER (), 0) AS decimal(10,2)) AS pct_of_plan_cache_mb
FROM plans
ORDER BY cache_mb DESC;
plan_typeplan_countcache_mbtotal_use_countavg_use_countpct_of_plan_cache_mb
Adhoc42255.5210032.3843.07
View31850.0923077.2538.87
Proc6419.782664.1615.35
Prepared363.212045.672.49
Trigger20.1731.500.13
Rule20.057035.000.04
UsrTab10.0411.000.03
Default30.03155.000.02

Two observations are worth keeping. First, Adhoc is the largest category at 43.07% of plan cache memory, with an average reuse count of only 2.38. That combination — large share, low reuse — is the signature of ad hoc cache waste. Second, View holds 38.87% with an average reuse of 7.25, which is the opposite pattern: meaningful memory but healthy reuse, which means the cost is justified. Stored procedure plans (Proc) are 15.35% with average reuse 4.16, which is also healthy. The conclusion is that on this instance plan-cache efficiency is bottlenecked specifically by the ad hoc workload, not by stored modules or views — any remediation should target ad hoc parameterisation and optimize for ad hoc workloads, not procedure cache hygiene.

Plan typeWatchMeaningOperational implication
Adhoc❌ if dominant and low-reuseOne-off or text-variant statements.Often the main source of plan-cache waste.
PreparedDependsParameterized client-side prepared statements.Usually more reusable than raw ad hoc plans.
Proc✅ when well reusedStored procedures and modules.Usually a more efficient cache occupant.
ViewDependsCached plans involving views.Can be normal on metadata-heavy or view-heavy systems.
TriggerDependsCached plans for DML or DDL triggers.Small unless triggers are numerous and complex.
UsrTabDependsCached plans for user-defined table types.Usually tiny.
Rule / DefaultDependsLegacy CREATE RULE / CREATE DEFAULT objects.Deprecated; use CHECK constraints and DEFAULT constraints.

SQL Server | sys.dm_exec_cached_plans | single-use ad hoc plans

The direct follow-up to the composition query is: how many of those ad hoc plans have usecounts = 1? A plan with usecounts = 1 was compiled once and has never been reused — it is pure plan-cache waste, and at any non-trivial volume it justifies enabling optimize for ad hoc workloads, which makes first-execution ad hoc statements cache only a compiled-plan stub instead of the full plan.

Count single-use ad hoc plans and sum the wasted memory

Immediately after the plan-cache composition query when Adhoc is dominant. It is typically triggered by ad hoc category occupying a large share of plan cache, user complaints about compile time. Read-only T-SQL, VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Cheap. Produce a single row summarising the count and memory of plans that will never be reused.

Count plans where usecounts = 1 and objtype = 'Adhoc', and sum their size_in_bytes as wasted megabytes.

SELECT
    COUNT(*) AS single_use_plans,
    CAST(SUM(size_in_bytes) / 1048576.0 AS decimal(18,2)) AS wasted_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
  AND objtype = 'Adhoc';
single_use_planswasted_mb
39953.78

There are 399 single-use ad hoc plans occupying about 53.78 MB. That is almost the full 55.52 MB that the composition query reported for the Adhoc category, which means essentially every ad hoc plan in cache is a one-shot statement that will never be reused. On a 24.7 GB instance that is about 0.2% of committed memory, so it is not catastrophic — but it is wasted, and the remediation is cheap: enabling optimize for ad hoc workloads replaces full plans with compiled-plan stubs on first execution, and the stubs are roughly a thousand times smaller. Combined with the optimize for ad hoc workloads = 0 finding from the configuration audit, this is a concrete, evidence-backed production recommendation rather than a theoretical best practice.

Value or PatternWatchMeaningOperational implication
Few single-use plans and small wasted_mb✅Ad hoc plan churn is minor.No urgent plan-cache action needed.
Many single-use plans with meaningful wasted_mb❌Large numbers of one-off statements are filling cache.Consider parameterization discipline and optimize for ad hoc workloads.
Single-use waste rising steadily across captures❌Cache churn is ongoing, not incidental.Investigate client query patterns and ad hoc workload design.
wasted_mb ≈ total Adhoc cache_mb❌Almost all ad hoc plans are one-shot.Enable optimize for ad hoc workloads.

SQL Server | sys.dm_os_memory_cache_counters | cache-store-level breakdown

sys.dm_os_memory_cache_counters is the next drill-down below sys.dm_os_memory_clerks. Where clerks aggregate by type, cache counters return one row per cache store with its current page usage, in-use page subset, entry count, and in-use entry count. It is the right view to answer “which cache store inside CACHESTORE_SQLCP is growing?” and to detect stores like USERSTORE_DBMETADATA and USERSTORE_TOKENPERM that do not have their own top-level clerk type but can silently grow under specific workloads.

SQL 2019+ column rename

On SQL Server 2019 and later the sys.dm_os_memory_cache_counters columns are pages_kb and pages_in_use_kb — not the older single_pages_kb and multi_pages_kb from pre-2012 builds. The old names still appear in some tutorials and throw Invalid column name errors on modern instances; always use the current schema.

List the top ten cache stores by total pages

Whenever the clerk view shows a plan-cache clerk growing and you want to identify the specific cache store driving the growth. It is typically triggered by CACHESTORE_* clerk climbing, USERSTORE_TOKENPERM suspected bloat, or a cache-entry leak investigation. Read-only T-SQL, VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Cheap. Show the largest cache stores with their total memory, active (in-use) subset, entry count, and the percentage of entries currently in use.

Return the top ten cache stores by total pages_kb with in-use memory and entry counts.

SELECT TOP (10)
    name AS cache_name,
    type AS cache_type,
    CAST(pages_kb / 1024.0 AS decimal(18,2)) AS pages_mb,
    CAST(pages_in_use_kb / 1024.0 AS decimal(18,2)) AS in_use_mb,
    entries_count,
    entries_in_use_count,
    CAST(100.0 * entries_in_use_count / NULLIF(entries_count, 0) AS decimal(10,2)) AS pct_entries_in_use
FROM sys.dm_os_memory_cache_counters
WHERE pages_kb > 0
ORDER BY pages_kb DESC;
cache_namecache_typepages_mbin_use_mbentries_countentries_in_use_countpct_entries_in_use
SQL PlansCACHESTORE_SQLCP66.510.81470132.77
Bound TreesCACHESTORE_PHDR50.480.0032800.00
Object PlansCACHESTORE_OBJCP19.990.005700.00
mssqlsystemresourceUSERSTORE_DBMETADATA11.34NULL272800.00
SchemaMgr StoreUSERSTORE_SCHEMAMGR8.10NULL00NULL
SystemRowsetStoreCACHESTORE_SYSTEMROWSET3.430.0034700.00
SystemRowsetStoreCACHESTORE_SYSTEMROWSET2.230.0024100.00
TokenAndPermUserStoreUSERSTORE_TOKENPERM2.20NULL47000.00
SystemRowsetStoreCACHESTORE_SYSTEMROWSET1.900.0019400.00
SystemRowsetStoreCACHESTORE_SYSTEMROWSET1.780.0020300.00

The top rows reproduce the picture from sys.dm_os_memory_clerks but at one more level of detail. SQL Plans (the ad hoc plan cache store inside CACHESTORE_SQLCP) holds 66.51 MB with only 13 of 470 entries currently in use — that low pct_entries_in_use of 2.77% is expected for a plan cache, because most cached plans are idle between executions. The Bound Trees store (CACHESTORE_PHDR) at 50.48 MB is second; its size is often proportional to the complexity of the views and modules the workload uses, and it is reasonable here. USERSTORE_DBMETADATA holds the metadata for the hidden mssqlsystemresource database (2,728 entries, 11.34 MB), which is always present. USERSTORE_TOKENPERM at 2.20 MB with 470 entries is the security-token cache — it is known to bloat on workloads with heavy dynamic-SQL impersonation or high connection churn with different security contexts, and watching it in isolation is worth doing on instances where application users frequently EXECUTE AS or use SQL-injected object references. The four CACHESTORE_SYSTEMROWSET rows are per-database metadata row caches, one row per hosted database.

Cache storeWatchMeaningOperational implication
CACHESTORE_SQLCP / SQL Plans❌ if growing without bounded reuseAd hoc and prepared plan cache.High entries_count with low entries_in_use_count = plan-cache waste.
CACHESTORE_OBJCP / Object Plans✅ when stableStored procedure plan cache.Usually stable and well-reused.
CACHESTORE_PHDR / Bound TreesDependsCompile-time bound-tree structures.Scales with complexity of views and modules.
USERSTORE_DBMETADATA / mssqlsystemresource✅System metadata for hidden resource db.Always present and usually stable.
USERSTORE_SCHEMAMGR / SchemaMgr StoreDependsSchema manager metadata cache.Grows with object count and schema complexity.
USERSTORE_TOKENPERM / TokenAndPermUserStore❌ if largeSecurity token and permission cache.Grows with dynamic-SQL impersonation and connection churn.
CACHESTORE_SYSTEMROWSETDependsPer-database system row-set metadata.One entry per hosted database; growth is workload-driven.

SQL Server | DBCC MEMORYSTATUS | canonical one-shot memory report

DBCC MEMORYSTATUS is the cross-cutting memory diagnostic that has been in SQL Server since SQL 2000. It returns more than thirty result sets in a single call: process/system counts, memory manager, per-NUMA-node memory, buffer pool details, procedure cache, query memory objects, optimization queues, small/medium/big gateways, and one section per memory clerk. It is the command Microsoft Support asks for first in any low-memory or OOM incident, because every other view can be reconstructed from its output. The format is subject to change between product releases, so it is a diagnostic tool rather than a scripted-monitoring input.

DBCC MEMORYSTATUS is a diagnostic artifact, not a monitoring target

The output format is officially documented as “subject to change between service packs and product releases.” Do not parse it programmatically into dashboards — use the underlying DMVs (sys.dm_os_memory_clerks, sys.dm_os_memory_cache_counters, sys.dm_exec_query_memory_grants) for that. Use DBCC MEMORYSTATUS for ad-hoc triage and for collecting a single artifact to attach to a support case.

Capture DBCC MEMORYSTATUS via docker exec sqlcmd

During any low-memory or OOM investigation, when Microsoft Support asks for it, or when a single DMV query is not enough to explain a memory anomaly. It is typically triggered by OOM incident, error 701 (insufficient memory), error 802 (insufficient buffer pool), MEMORYCLERK_* clerks growing inexplicably. Runs from sqlcmd — either a T-SQL session (but pyodbc and some ORMs cannot walk the 30+ result sets) or a shell command via docker exec stoxx-db. Read-only. Cheap to run. Collect a single, comprehensive memory-state artifact that covers every clerk and every memory component in one output.

Run DBCC MEMORYSTATUS inside the stoxx-db container via sqlcmd, suppress info messages, and render as plain text.

docker exec stoxx-db bash -c "/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P EsgDev2026Pass1 -C -N -Q 'DBCC MEMORYSTATUS WITH NO_INFOMSGS' -W -h-1"
Available Physical Memory 23108452352
Available Virtual Memory 70364449144833
Available Paging File 23108452352
Working Set 4294967296
Percent of Committed Memory in WS 100
Page Faults 0
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
 
(10 rows affected)
VM Reserved 33004636
VM Committed 1943212
Locked Pages Allocated 0
Large Pages Allocated 133120
Emergency Memory 1024
Emergency Memory In Use 8
Target Committed 23250920
Current Committed 1943216
Pages Allocated 886840
Pages Reserved 0
Pages Free 829024
Pages In Use 623896
Page Alloc Potential 22598408
NUMA Growth Phase 0
Last OOM Factor 0
Last OS Error 0
 
(16 rows affected)

The first result set is Process/System Counts: Available Physical Memory = 23,108,452,352 bytes (≈ 22.0 GB) matches sys.dm_os_sys_memory.available_physical_memory_kb. The Working Set = 4,294,967,296 bytes (4 GB) matches the physical_memory_in_use_mb = 4226 reported earlier. System physical memory high = 1 is the flag equivalent of the system_memory_state_desc = 'Available physical memory is high' we saw in sys.dm_os_sys_memory. All four low-memory flags are 0. The second result set is Memory Manager: VM Reserved (33.0 GB) is the total VAS SQL Server has claimed, almost all of which is unused reservation. VM Committed (1.94 GB) matches sys.dm_os_sys_info.committed_mb. Target Committed (23.25 GB) matches target_mb. Large Pages Allocated at 133,120 bytes confirms SQL Server is using some transparent huge pages on this Linux host. The command returns many more sections after these two — one per memory clerk, plus buffer pool details, procedure cache, and query memory objects. Capture the full output with > /tmp/dbcc_memstatus.txt inside the container and attach it to an incident ticket when memory pressure is suspected.

SectionWhat it reportsWhen to read it
Process/System CountsOS and process-level physical/virtual memory, low-memory flags.First pass — external vs internal pressure.
Memory ManagerSQL Server’s internal VAS reserved/committed, target, pages allocated.Second pass — is SQL Server trying to grow or trim?
Memory node Id = NPer-NUMA-node memory accounting.Only on multi-NUMA hosts when you suspect node-local skew.
Memory Clerk ManagerOne section per clerk with Pages Allocated, VM Reserved, VM Committed.When a specific clerk is suspected of leaking.
Buffer PoolDatabase, Dirty, Latched, In IO, Page Life Expectancy.Buffer pool health at a glance.
Procedure CachePlan cache buckets and sizes.Plan-cache bloat investigation.
Query Memory ObjectsGrant counts, waiters, available workspace memory.Memory-grant investigation (same info as sys.dm_exec_query_resource_semaphores).
Optimization Queue and GatewaysSmall/medium/big query-compile gateways.RESOURCE_SEMAPHORE_QUERY_COMPILE waits.

Memory Grants

Two-view memory grant surface

Memory grants are workspace memory (for sorts, hashes, spools, and similar operators) that SQL Server pre-allocates to a query before it runs. The grant comes from a finite pool managed by resource semaphores, and when the pool is exhausted the query waits with a RESOURCE_SEMAPHORE wait type. Two DMVs cover this surface completely:

  • sys.dm_exec_query_memory_grants — one row per active grant, including queries currently running with a grant (grant_time IS NOT NULL) and queries waiting for one (grant_time IS NULL).
  • sys.dm_exec_query_resource_semaphores — the upstream pool view: one row per resource semaphore (regular vs small-query) per resource pool, with target_memory, available_memory, granted_memory, grantee_count, and waiter_count.

A grant problem is visible in both: if waiter_count > 0 in the semaphore view, the memory-grants view will have rows with grant_time IS NULL and rising wait_time_ms. Conversely, a busy instance with many active grants but zero waiters is healthy.

SQL Server | sys.dm_exec_query_memory_grants | inspect active and queued memory grants

sys.dm_exec_query_memory_grants returns one row for every query that currently holds or is waiting for a memory grant. The two canonical filters are grant_time IS NOT NULL (active grants — useful for capturing who is holding workspace memory right now) and grant_time IS NULL (queued — useful for confirming pressure). A production capture usually wants both, joined to sys.dm_exec_sql_text for the statement text so the operator can correlate the grant to a real client query.

Inspect currently active and waiting memory grants

During any memory-grant pressure investigation, whenever RESOURCE_SEMAPHORE appears in sys.dm_os_wait_stats, or when batch jobs complain about query timeouts. It is typically triggered by RESOURCE_SEMAPHORE waits, user complaint about slow sorts/hashes, OOM suspected from huge grants. Read-only T-SQL. Requires VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Cheap. List every query that currently holds or is waiting for workspace memory, with the requested/granted/used columns that diagnose over-estimation, under-estimation, and pool exhaustion.

Join sys.dm_exec_query_memory_grants to sys.dm_exec_sql_text via CROSS APPLY, exclude the capturing session, and show each grant with its request/granted/used columns plus a short SQL text excerpt. Captured via race_demo.py while a second session runs a deliberate large ORDER BY on dbo.demo_idxmaint_rowstore with OPTION (MAXDOP 1) to generate a real grant.

SELECT TOP (5)
    mg.session_id,
    CONVERT(varchar(23), mg.request_time, 121) AS request_time,
    CONVERT(varchar(23), mg.grant_time, 121) AS grant_time,
    mg.requested_memory_kb / 1024 AS requested_mb,
    mg.granted_memory_kb / 1024 AS granted_mb,
    mg.required_memory_kb / 1024 AS required_mb,
    mg.used_memory_kb / 1024 AS used_mb,
    mg.max_used_memory_kb / 1024 AS max_used_mb,
    mg.queue_id,
    mg.wait_time_ms / 1000.0 AS wait_sec,
    mg.dop,
    mg.timeout_sec,
    LEFT(REPLACE(REPLACE(LTRIM(st.text), CHAR(13), ' '), CHAR(10), ' '), 80) AS sql_text
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
WHERE mg.session_id <> @@SPID
ORDER BY mg.requested_memory_kb DESC;
session_idrequest_timegrant_timerequested_mbgranted_mbrequired_mbused_mbmax_used_mbqueue_idwait_secdoptimeout_secsql_text
552026-04-11 16:35:42.9502026-04-11 16:35:42.950696904949NULLNULL11166WAITFOR DELAY '00:00:00.200'; SELECT TOP 500000 ColA, ColB, ColC FRO

This capture is the “healthy” shape of an active grant. Session 55 requested about 69 MB of workspace memory and received it immediately — request_time and grant_time are identical at millisecond resolution, meaning the grant was fulfilled without queueing. queue_id and wait_sec are NULL because the query never entered a queue. The query is currently using 49 MB of the 69 MB granted (used_mb), which is a 71% utilisation — the gap between granted and used is the optimizer’s estimation buffer. In steady state, memory grant feedback (active on SQL 2022 with compat level 140+) will progressively tune that gap downward for repeated executions of the same plan. dop = 1 confirms the query is running serially because of the OPTION (MAXDOP 1) hint; a parallel plan with DOP 8 would have received 8× this grant. The unusual timeout_sec = 1166 (almost 20 minutes) comes from the query wait (s) = -1 setting: SQL Server computes the timeout as 25× the estimated query cost, so a big sort gets a long timeout by default.

Column or PatternValueWatchMeaningImplication
Result setNo rows (user queries)✅No user query currently holds or waits for a grant.Workspace memory is idle.
grant_timeEqual to request_time✅Grant fulfilled instantly.No semaphore pressure.
grant_timeNULL❌Query is still waiting in the grant queue.Investigate semaphore pool and pressure.
queue_id0DependsSmall-query gateway (grant < 5 MB and cost < 3 units).Small queries queueing usually means the main pool is saturated.
queue_id1DependsRegular memory-grant queue.Large grants queueing means workspace exhaustion.
used_mb / granted_mb ratioNear 1.0✅Grant sizing matches actual usage.Healthy; memory grant feedback has converged.
used_mb / granted_mb ratioMuch less than 1.0❌Grant is over-sized.Fix stats, indexes, or let memory grant feedback adjust.
max_used_mb > granted_mbDependsGrant was too small and query spilled.Spill to tempdb; fix cardinality estimates.
dopHigh on small queries❌Unnecessary parallelism multiplies grants.Cap DOP at the workload group or query level.
wait_secRising❌Pressure is actively delaying execution.Kill runaway grants, add memory, or shape workload.
timeout_secSmall and ticking down❌Query is close to giving up the grant request.Expect error 8645 at timeout.

SQL Server | sys.dm_exec_query_resource_semaphores | inspect resource semaphore pools

The upstream view for memory grants is the semaphore. A resource semaphore is the memory pool the grants come from: there are exactly two per Resource Governor resource pool — one regular (resource_semaphore_id = 0) and one small-query gateway (resource_semaphore_id = 1) — and every SQL Server instance always has at least the default and internal resource pools, so a minimum of four rows always appear.

Small-query gateway conditions

A query uses the small-query semaphore (id 1) only if both conditions hold: requested grant < 5 MB and estimated query cost < 3 cost units. Anything larger than that goes through the regular semaphore (id 0). The small-query gateway exists so that many tiny queries do not queue behind one expensive sort.

List every resource semaphore with target memory and current state

As the upstream check after sys.dm_exec_query_memory_grants shows waiters, or when RESOURCE_SEMAPHORE / RESOURCE_SEMAPHORE_SMALL_QUERY waits appear in sys.dm_os_wait_stats. It is typically triggered by RESOURCE_SEMAPHORE wait accumulation, Resource Governor pool tuning, workspace memory configuration change. Read-only T-SQL. Requires VIEW SERVER PERFORMANCE STATE (SQL 2022+) or VIEW SERVER STATE (older). Confirm the target and available workspace memory per pool and per semaphore, and detect whether waiters are present or forced grants have been issued.

Return one row per resource semaphore per pool with target, available, granted, used, grantee, and waiter columns.

SELECT
    resource_semaphore_id,
    pool_id,
    target_memory_kb / 1024 AS target_memory_mb,
    max_target_memory_kb / 1024 AS max_target_mb,
    total_memory_kb / 1024 AS total_memory_mb,
    available_memory_kb / 1024 AS available_memory_mb,
    granted_memory_kb / 1024 AS granted_memory_mb,
    used_memory_kb / 1024 AS used_memory_mb,
    grantee_count,
    waiter_count,
    timeout_error_count,
    forced_grant_count
FROM sys.dm_exec_query_resource_semaphores
ORDER BY resource_semaphore_id, pool_id;
resource_semaphore_idpool_idtarget_memory_mbmax_target_mbtotal_memory_mbavailable_memory_mbgranted_memory_mbused_memory_mbgrantee_countwaiter_counttimeout_error_countforced_grant_count
0115119163581511915119000000
0216358163581635816358000000
11704NULL7047040000NULLNULL
12704NULL7047031010NULLNULL

Four rows: two regular semaphores (id 0) and two small-query semaphores (id 1), one of each per resource pool. The regular semaphore for the default pool (pool_id 2) has target_memory_mb = 16,358 (about 16 GB), which is the workspace-memory budget SQL Server has allocated to non-internal queries out of the max server memory. granted_memory_mb = 0 and waiter_count = 0 mean the pool is idle for user queries at capture time. The small-query semaphore for the default pool is more interesting: granted_memory_mb = 1, grantee_count = 1, waiter_count = 0. That single grantee is the live capture query held by the run_queries.py runner itself — a small grant (< 5 MB) sitting inside the small-query gateway. available_memory_mb = 703 confirms the pool has almost its full 704 MB budget free. timeout_error_count and forced_grant_count are NULL for the small-query semaphore because those counters are only tracked on the regular semaphore. The important reading for production: as long as waiter_count = 0 on all rows and available_memory_mb stays close to target_memory_mb, memory grants are not a bottleneck.

ColumnValue or PatternWatchMeaningImplication
waiter_count0 on every row✅No query is waiting for a grant.Grants are not the bottleneck.
waiter_count> 0 on the regular semaphore❌Regular grant queue is active.Investigate large grants and over-estimation.
waiter_count> 0 on the small-query semaphore❌Small queries are queueing.The small pool is full; usually means many small queries running at once.
available_memory_mbClose to target_memory_mb✅Pool is not under pressure.Normal.
available_memory_mbNear 0 with grantees present❌Pool is exhausted.Expect waiter_count > 0 and rising wait times.
forced_grant_countGrowing since startup❌SQL Server has issued minimum-size grants under pressure.Indicates chronic memory shortage; not a one-off event.
timeout_error_countGrowing since startup❌Queries are giving up on the grant request.Error 8645 is firing; clients see query timeouts.
target_memory_mbMuch smaller than expected❌Workspace memory budget is too small.Investigate max server memory, Resource Governor, or memory pressure.
pool_id = 1internal poolContextReserved for SQL Server internal queries.Usually untouched by user workloads.
pool_id = 2default poolContextWhere user queries run unless Resource Governor routes them elsewhere.The primary pool to monitor.

SQL Server | memory grant diagnosis | triage decision flow

Once both the grants and semaphores views have been captured, the next question is what to do about any pressure found. The decision tree below walks from the first observation (are rows present in sys.dm_exec_query_memory_grants with grant_time IS NULL?) through the two branches that usually explain real incidents: oversized individual grants driven by bad cardinality estimates, or many moderate grants driven by workload concurrency. Each leaf points at a concrete fix, from statistics refresh to DOP reduction to Resource Governor shaping.


flowchart TD
    A["Queries waiting on memory grants"] --> B{"Rows in sys.dm_exec_query_memory_grants<br/>with grant_time IS NULL?"}
    B --> Y1([YES])
    B --> N1([NO])
    N1 --> C["Pressure is not active now<br/>check waits and recapture during incident"]
    Y1 --> D{"Requested grants huge<br/>per query?"}
    D --> Y2([YES])
    D --> N2([NO])
    Y2 --> E["Check stale statistics,<br/>bad cardinality estimates,<br/>missing indexes, and oversize DOP"]
    N2 --> F{"Many moderate grants<br/>at once?"}
    F --> Y3([YES])
    F --> N3([NO])
    Y3 --> G["Concurrency issue<br/>review workload shaping,<br/>MAXDOP, and Resource Governor"]
    N3 --> H["Inspect the specific waiting query text,<br/>plan shape, and grant feedback behavior"]

    classDef yesNode fill:#1f3b2d,stroke:#73d13d,stroke-width:2px,color:#c0caf5,font-weight:bold;
    classDef noNode fill:#4a1f24,stroke:#db4b4b,stroke-width:2px,color:#c0caf5,font-weight:bold;
    class Y1,Y2,Y3 yesNode;
    class N1,N2,N3 noNode;
Common causeTypical fix
max server memory set too lowRaise it if the host has real headroom.
Cardinality estimates far too highRefresh statistics and fix estimation errors.
Large sorts or hashes due to poor indexingAdd or adjust indexes to reduce worktable demand.
Excessive DOPReduce DOP or fix parallel plan shape.
Many concurrent grant-heavy queriesShape workload concurrency or use Resource Governor.

Configuration and Intervention Commands

Primary, secondary, and intervention commands

This section is organised in three groups. The primary commands set the two configuration values that most directly affect memory stability: max server memory (MB) and optimize for ad hoc workloads. The secondary commands tune min server memory (MB), min memory per query (KB), index create memory (KB), and query wait (s) — rarely needed, but worth documenting so operators can recognise when someone else has changed them. The intervention commands flush plan cache or drop clean buffer pool pages; they are not routine tuning and are reserved for controlled troubleshooting or targeted remediation.

None of the sp_configure or DBCC commands in this section are executed against live stoxx — this instance runs with defaults on purpose so the rest of the note can show the default state. They are shown as remediation patterns.

SQL Server | sp_configure | set explicit max server memory cap

max server memory (MB) caps the buffer pool and most memory clerks, but not the total sqlservr process memory. On Linux, the total process cap is memory.memorylimitmb; max server memory must be set lower than that by at least 1–2 GB to leave headroom for SQLPAL, CLR, LibOS, Agent, Full-Text, and any other in-process components. On Windows, max server memory is typically the only cap, and it should sit 10–25% below host RAM depending on OS footprint and colocated processes.

Set an explicit max server memory cap (pattern)

During controlled maintenance — the value change is dynamic (no restart required) but it can cause an immediate trim if the new value is well below current consumption, which evicts cached pages and temporarily degrades performance. It is typically triggered by post-install hardening, post-migration baseline, remediation after an OOM or host-pressure incident. T-SQL session with ALTER SETTINGS server-level permission (sysadmin or serveradmin). State-changing command — the new value is persisted in sys.configurations and takes effect on RECONFIGURE. No restart required. Bound SQL Server’s buffer pool growth so the host and other in-process components have guaranteed headroom.

The default max server memory is effectively unlimited

With max server memory (MB) = 2147483647 (the post-install default), SQL Server can grow until either memory.memorylimitmb on Linux, the Windows working-set trim signal, or an OOM event intervenes. That is not a production-safe steady state, and the first configuration change on any new instance should be to set an explicit value.

Size the cap against the total process budget

On the stoxx host with about 24,732 MB visible to SQL Server, a dedicated instance would typically use max server memory (MB) = 22684 (approximately 92% of visible RAM), leaving about 2 GB for SQLPAL, CLR, LibOS, Agent, and the OS kernel. On hosts where SQL Server shares memory with other processes, lower the cap proportionally.

Set max server memory (MB) to 22684 and make the change effective via RECONFIGURE. Pattern — not executed against stoxx.

EXEC sp_configure 'max server memory (MB)', 22684;
RECONFIGURE;

Verify the new max server memory cap is in effect

Immediately after the previous command, to confirm the RECONFIGURE was successful. It is typically triggered by follow-up verification of any sp_configure change. Read-only T-SQL, any session. Cheap. Confirm that value and value_in_use now both reflect the new cap.

Re-read sys.configurations for max server memory (MB) after the RECONFIGURE. The live capture below is from the stoxx instance, which is still at the unlimited default because the preceding sp_configure pattern is not executed here.

SELECT
    name,
    CAST(value AS bigint) AS value,
    CAST(value_in_use AS bigint) AS value_in_use
FROM sys.configurations
WHERE name = 'max server memory (MB)';
namevaluevalue_in_use
max server memory (MB)21474836472147483647

Both columns still show the unlimited default of 2,147,483,647. On a production instance where the sp_configure pattern above has been run, value and value_in_use should reflect the new cap (e.g. 22684) — if value has been set but value_in_use is still at the old number, the RECONFIGURE did not run and the change has not taken effect.

SQL Server | sp_configure | enable optimize for ad hoc workloads

optimize for ad hoc workloads changes the plan cache behavior so that the first execution of an ad hoc statement stores only a compiled-plan stub (roughly 300 bytes) instead of the full plan. On second execution, the full plan is cached; subsequent executions hit cache as normal. The net effect is that one-shot statements waste 300 bytes instead of ~150 KB each, and only genuinely reusable ad hoc statements get full plans. On the stoxx instance the direct evidence — 399 single-use ad hoc plans wasting 53.78 MB — makes this an evidence-backed recommendation.

Enable optimize for ad hoc workloads (pattern)

On any instance where single-use ad hoc plans occupy a meaningful share of plan cache and the workload cannot be re-parameterised at the client. It is typically triggered by single-use ad hoc waste > ~20 MB on small instances or > ~100 MB on large instances, with ad hoc plans dominating CACHESTORE_SQLCP. T-SQL session with ALTER SETTINGS. State-changing, dynamic, no restart. Existing plans are unaffected — the new behavior applies to new cache entries. Stop one-shot ad hoc statements from wasting plan cache memory on first execution.

Do not enable on genuinely parameterised workloads

If the workload is already well-parameterised (for example, all client code uses sp_executesql or prepared statements), enabling optimize for ad hoc workloads costs a second compilation on every genuinely-reused statement with no benefit. Verify ad hoc dominance in sys.dm_exec_cached_plans before flipping the switch.

Evidence from the current stoxx capture

On this instance: optimize for ad hoc workloads = 0, Adhoc plans are 43.07% of plan cache with average reuse 2.38, and 399 single-use ad hoc plans waste 53.78 MB. Enabling the setting is the correct remediation here.

Set optimize for ad hoc workloads = 1 and reconfigure. Pattern — not executed against stoxx.

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

SQL Server | sp_configure | secondary memory settings

Four secondary memory-related settings are useful mostly to recognise when someone else has changed them. Their defaults are correct on almost every workload. Change them only with a specific problem in hand.

SettingDefaultWhat it controlsWhen to change
min server memory (MB)0Lower bound SQL Server will not trim below.Rarely — only on contested Windows hosts with multiple instances or when Windows is trimming the working set under external pressure.
min memory per query (KB)1024 (1 MB)Minimum workspace memory grant per query.Raise to 4096 or 8192 on workloads with many small sorts that routinely spill.
index create memory (KB)0 (auto)Memory grant for CREATE INDEX sort.Raise on offline index rebuilds of very large tables that spill to tempdb.
query wait (s)-1 (formula)Memory-grant timeout. Default is 25 * query_cost_seconds.Rarely — positive overrides usually cause more timeouts, not fewer.

Raise min memory per query (pattern)

After observing many small queries consistently spilling to tempdb with hash_warning or sort_warning events and when indexing is not a viable fix. It is typically triggered by high sort_warning / hash_warning counters with small-grant queries. T-SQL, ALTER SETTINGS, dynamic. State-changing. Give every query at least 4 MB of workspace memory instead of the 1 MB default.

Raise min memory per query (KB) from 1024 to 4096. Pattern.

EXEC sp_configure 'min memory per query (KB)', 4096;
RECONFIGURE;

Raise index create memory (pattern)

Before a planned offline rebuild of a very large table when the current auto-sized grant is spilling. It is typically triggered by CREATE INDEX taking hours longer than expected with tempdb spill in the actual plan. T-SQL, ALTER SETTINGS, dynamic. State-changing. Give CREATE INDEX a fixed grant instead of the default auto-sized grant.

Raise index create memory (KB) to 2097152 (2 GB). Pattern — reset to 0 after the rebuild to restore auto-sizing.

EXEC sp_configure 'index create memory (KB)', 2097152;
RECONFIGURE;

SQL Server | memory grant feedback | adaptive memory grant tuning

Memory grant feedback is the SQL 2017/2019/2022 adaptive query processing feature that lets the engine learn from a query’s past memory usage and adjust the next grant accordingly. It exists in three waves: batch mode memory grant feedback (SQL 2017, compat level 140), row mode memory grant feedback (SQL 2019, compat level 150), and percentile + persistence memory grant feedback (SQL 2022, compat level 140, requires Query Store in read_write mode). The SQL 2022 percentile algorithm looks at the distribution of recent grants for a plan instead of only the last execution, which fixes oscillating-workload bugs in the earlier waves.

Edition and compat level requirements

Memory grant feedback is Enterprise / Developer only. SQL Server Standard and Web editions do not support it. Batch mode requires compat 140+, row mode requires compat 150+, and percentile + persistence requires compat 140+ and Query Store READ_WRITE.

Check the current memory grant feedback database-scoped configuration

As the first step when investigating query memory grants on SQL 2019+, before assuming the optimizer is over-estimating. It is typically triggered by user complaint about over-grants, oscillating workloads, or pre-migration compatibility audit. Read-only T-SQL against sys.database_scoped_configurations. Requires VIEW DATABASE STATE. Confirm whether the current database has memory grant feedback enabled and which of the three waves (batch/row/persistence/percentile) are active.

Read the four memory-grant-feedback scoped configuration options from sys.database_scoped_configurations in the current database context (stoxx).

USE stoxx;
SELECT
    name,
    value,
    value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (
    'BATCH_MODE_MEMORY_GRANT_FEEDBACK',
    'ROW_MODE_MEMORY_GRANT_FEEDBACK',
    'MEMORY_GRANT_FEEDBACK_PERSISTENCE',
    'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT'
);
namevaluevalue_for_secondary
BATCH_MODE_MEMORY_GRANT_FEEDBACKTrue
ROW_MODE_MEMORY_GRANT_FEEDBACKTrue
MEMORY_GRANT_FEEDBACK_PERSISTENCETrue
MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANTTrue

All four memory-grant-feedback options are enabled on the stoxx database at default settings. The four flags correspond to the three waves of the feature: BATCH_MODE_MEMORY_GRANT_FEEDBACK (SQL 2017, compat 140+, batch-mode operators), ROW_MODE_MEMORY_GRANT_FEEDBACK (SQL 2019, compat 150+, row-mode operators), MEMORY_GRANT_FEEDBACK_PERSISTENCE (SQL 2022, persists feedback in Query Store so it survives cache evictions), and MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT (SQL 2022, percentile-based algorithm that considers the distribution of past grants rather than only the most recent one). The value_for_secondary column is empty because these options are instance-level and do not have distinct secondary-replica values on this instance. All four True is the expected default for SQL Server 2022 Developer Edition with Query Store enabled. If a memory-grant-feedback-related incident requires disabling one of these, do it one wave at a time (start with percentile, then persistence, then row mode, then batch mode) rather than disabling all four at once.

OptionCurrentWatchMeaningImplication
BATCH_MODE_MEMORY_GRANT_FEEDBACKTrue✅Batch-mode adaptive memory grant tuning is on.Required for columnstore and batch-mode row store workloads to self-tune grants.
ROW_MODE_MEMORY_GRANT_FEEDBACKTrue✅Row-mode adaptive memory grant tuning is on.Covers the traditional row-mode operators (most OLTP workloads).
MEMORY_GRANT_FEEDBACK_PERSISTENCETrue✅Feedback is persisted to Query Store between cache evictions.Feedback survives plan cache flushes and server restarts.
MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANTTrue✅Percentile-based algorithm across multiple past executions.Fixes the oscillation bug in the earlier two waves for workloads with variable grant requirements.

Disable memory grant feedback for a specific query (pattern)

When a single known-bad plan is being repeatedly adjusted in the wrong direction by memory grant feedback and the fix is to force the optimizer’s initial estimate instead. It is typically triggered by memory_grant_updated_by_feedback extended event firing repeatedly on one plan with poor outcome. T-SQL, anyone with EXECUTE permission on the query can add the hint. Changes the specific query only — no configuration change. Non-state-changing at the server level. Bypass the adaptive feedback for one problematic plan while leaving it active for everything else.

Add the DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK hint to a specific query. The hint is a single-query override that takes precedence over database-scoped and instance-wide settings.

SELECT col1, col2, col3
FROM dbo.some_table
WHERE indexed_col = 42
OPTION (USE HINT('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Disable memory grant feedback persistence for an entire database (pattern)

When a whole workload is oscillating badly under percentile feedback and the per-query hint is not practical. It is typically triggered by widespread oscillation across many plans; percentile is making grants worse rather than better. T-SQL in the target database context. Requires ALTER DATABASE SCOPED CONFIGURATION. State-changing. Stop persisting feedback to Query Store while still allowing in-memory per-plan feedback.

Disable MEMORY_GRANT_FEEDBACK_PERSISTENCE for the current database. Disabling persistence also drops any feedback already collected.

ALTER DATABASE SCOPED CONFIGURATION
SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

SQL Server | DBCC FREEPROCCACHE | plan-cache flush patterns

The DBCC FREEPROCCACHE family of commands drops cached plans from memory. They are disruptive — every subsequent query has to recompile, which causes a burst of CPU and compile waits — and should only be used when a narrower fix is not available. Prefer the narrowest possible scope (one plan, one database) over a full cache flush.

DBCC FREEPROCCACHE is a production hammer

Running DBCC FREEPROCCACHE on a production instance drops every cached plan immediately. Every subsequent query recompiles from scratch, causing a CPU spike, compile-lock contention (RESOURCE_SEMAPHORE_QUERY_COMPILE waits), and often a transient latency spike visible to end users. This is never a routine tuning step.

Prefer narrower scopes

Four alternatives to DBCC FREEPROCCACHE without arguments, ordered from least to most disruptive:

  • DBCC FREEPROCCACHE(<plan_handle>) — drops one specific plan.
  • ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE — drops the current database’s procedure cache only.
  • DBCC FLUSHPROCINDB(DB_ID('<db>')) — drops one database’s cache from any session.
  • DBCC FREEPROCCACHE with no arguments — instance-wide, use only in lab or scheduled maintenance.

Clear one cached plan by plan_handle (safest)

When one specific problematic plan has been identified (from sys.dm_exec_query_stats or an execution-plan review) and needs to be recompiled on next execution. It is typically triggered by parameter-sniffing issue, stale plan after statistics update, post-schema-change. T-SQL, ALTER SERVER STATE permission. State-changing. Minimum blast radius. Force recompilation of one specific statement without disturbing any other cached plan.

Drop one specific plan by handle. Replace <plan_handle> with the actual varbinary(64) value from sys.dm_exec_query_stats.plan_handle.

DBCC FREEPROCCACHE(<plan_handle>);

Clear only the current database’s procedure cache

When a database-scoped workload has gone pathological and the operator wants to force all plans for that database to recompile without touching other databases on the same instance. It is typically triggered by mass statistics update, ETL load, database-scoped parameter sniffing issue. T-SQL in the target database. Requires ALTER DATABASE SCOPED CONFIGURATION. State-changing. Affects only the current database. Recompile everything in one database without affecting the other databases on the instance.

Clear the procedure cache for the current database only.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Clear one database’s procedure cache from any session

Same scenarios as the scoped-configuration variant, but when the operator is not in the target database context and does not want to USE it. It is typically triggered by cross-database remediation from a DBA utility session. T-SQL, ALTER SERVER STATE. State-changing. Affects only the specified database. Drop one database’s cache from any session via DB_ID().

Drop the cached plans for stoxx only.

DBCC FLUSHPROCINDB(DB_ID('stoxx'));

Clear the entire instance plan cache (last resort)

Only in lab environments, during scheduled maintenance windows, or when no narrower alternative works and the operator has accepted the latency spike. It is typically triggered by controlled cold-cache benchmark, a post-incident cleanup where the whole cache is suspect. T-SQL, ALTER SERVER STATE. State-changing. Maximum blast radius — every plan in cache is dropped. Reset the plan cache entirely. Every subsequent query will recompile.

Never run on busy production without a maintenance window

The command is instant, but the compile storm that follows can last seconds to minutes on a busy instance. On high-throughput OLTP workloads, this can cause transient timeouts and connection pileups.

Drop every cached plan on the instance.

DBCC FREEPROCCACHE;

SQL Server | CHECKPOINT + DBCC DROPCLEANBUFFERS | cold-cache buffer flush

Dropping clean pages from the buffer pool is how SQL Server simulates a cold cache for benchmarking. Dirty pages must be written first (CHECKPOINT), then clean pages can be dropped (DBCC DROPCLEANBUFFERS). After the combined command, the next query read will hit disk, which makes it useful for measuring first-read cost but brutally slow on production.

Do not run DROPCLEANBUFFERS on production

DBCC DROPCLEANBUFFERS drops every clean cached page from the buffer pool. On a production workload, this causes an immediate storm of physical reads as queries reload their working set from disk, with visible latency spikes and high PAGEIOLATCH_* waits for the next few seconds to minutes.

Use only in a lab or isolated benchmark environment

The only legitimate use is reproducing a cold-cache read benchmark in a test or staging environment where the instance is dedicated to the benchmark and no real users depend on cached data.

Checkpoint dirty pages then drop clean buffer pool pages

Only in benchmark or lab environments where cold-cache behavior needs to be reproduced reliably. It is typically triggered by controlled benchmark or test of a specific query’s first-read performance. T-SQL, sysadmin. CHECKPOINT is state-changing (flushes dirty pages); DBCC DROPCLEANBUFFERS drops clean pages. Kept as one atomic operation because the DROPCLEANBUFFERS without a prior CHECKPOINT would leave dirty pages in memory and skew the benchmark. Produce a cold buffer pool so the next query read comes from disk.

Checkpoint dirty pages to disk, then drop all clean pages from the buffer pool. Lab / benchmark only.

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

Windows-Only LPIM

Windows-only territory

Lock Pages in Memory (LPIM) is a Windows-specific privilege that lets the SQL Server service account allocate physical memory that the OS cannot page out to the Windows page file. It does not apply to Linux in the same form — on Linux, SQL Server achieves equivalent anti-paging behavior through its cgroup-aware memory management and the memory.memorylimitmb cap. This section exists in the note because the same sql_memory_model_desc check is how you verify LPIM on Windows after granting the privilege and restarting the service, and because locked_page_allocations_mb = 0 is a signal every operator reads — the meaning of zero differs depending on whether the host is Windows or Linux.

SQL Server | sys.dm_os_sys_info | verify the memory model

On Windows, the expected value of sql_memory_model_desc after granting LPIM and restarting the SQL Server service is LOCK_PAGES. If it still reads CONVENTIONAL, either the privilege was not granted correctly, the service account does not have SeLockMemoryPrivilege, or the service was not restarted after the privilege change. On Linux, the expected and only value is CONVENTIONAL — this is not a defect to fix.

Inspect sql_memory_model and sql_memory_model_desc

On Windows, immediately after granting LPIM and restarting the SQL Server service, to verify the privilege has taken effect. On Linux, as a quick confirmation that the memory model is the expected CONVENTIONAL. It is typically triggered by post-install verification on Windows, or a report that LPIM does not appear to be active. Read-only T-SQL against sys.dm_os_sys_info. VIEW SERVER STATE / VIEW SERVER PERFORMANCE STATE. Confirm which memory model SQL Server is currently using.

Return the numeric memory model and its text description.

SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;
sql_memory_modelsql_memory_model_desc
1CONVENTIONAL

The capture shows sql_memory_model = 1 and sql_memory_model_desc = CONVENTIONAL. On the stoxx Linux Docker host, that is the expected result — LPIM is Windows-only, so there is nothing to verify and nothing to fix. On a Windows production host, the same query after granting SeLockMemoryPrivilege to the service account and restarting the service should return 2 / LOCK_PAGES. If it still shows CONVENTIONAL, the privilege was not granted effectively: verify secpol.msc → Local Policies → User Rights Assignment → Lock pages in memory includes the SQL Server service account SID, then restart the MSSQLSERVER service. LARGE_PAGES (3) is a specialist configuration enabled via trace flag 834 — it requires LPIM plus enough large pages to be available at SQL Server startup and is rarely used outside highly controlled performance benchmarks.

ValueNumericWatchMeaningImplication
CONVENTIONAL1ContextStandard memory model.Expected on Linux; means LPIM is not active on Windows.
LOCK_PAGES2✅ on tuned Windows serversLocked pages are active — SQL Server’s allocations cannot be paged out by Windows.Requires SeLockMemoryPrivilege granted to the service account and a correctly set max server memory cap.
LARGE_PAGES3DependsLarge-page allocations via trace flag 834.Specialist configuration; validate carefully before using.

LPIM without an explicit max server memory cap is dangerous

On Windows, granting LPIM without setting max server memory (MB) is a worst-case configuration. SQL Server can acquire every physical page on the host and mark them locked, which the OS cannot reclaim. This starves the kernel, other processes, and eventually triggers a system-wide hang or an OS-level out-of-memory condition.

Always pair LPIM with an explicit max server memory cap

The correct Windows sequence is (1) set an explicit max server memory (MB) that leaves at least 1–2 GB for the OS and other in-process components, (2) grant Lock pages in memory to the SQL Server service account, (3) restart the service, (4) run the sys.dm_os_sys_info query to verify LOCK_PAGES.

SQL Server Memory and the Buffer Pool Recommendations

Twenty-four concrete actions grouped by category. Every recommendation below is either validated by the live captures in this note or is a Microsoft Learn / vendor-recommended baseline for the Linux + Docker + SQL Server 2022 Developer Edition configuration on stoxx.

Host sizing and Linux memory boundaries

  • Set memory.memorylimitmb explicitly. Do not rely on the 80% default. Set it below the cgroup limit (if present) and below host RAM, leaving headroom for the Linux kernel and colocated processes. Pattern: mssql-conf set memory.memorylimitmb 22000 on a host with /proc/meminfo MemTotal around 31 GB.
  • Size cgroup v2 memory.max deliberately. On Kubernetes and Docker, set an explicit --memory or resources.limits.memory that is lower than host RAM. SQL Server 2022 CU20+ and SQL Server 2025 honor cgroup v2 directly; on older builds, use memory.memorylimitmb as the backstop.
  • Leave swappiness at low values. Linux hosts running SQL Server should set vm.swappiness = 1 so the kernel avoids swapping the SQL Server working set. On the stoxx host this is documented in 01-server-configuration but remains unchanged.
  • Disable transparent huge pages default auto-defrag. /sys/kernel/mm/transparent_hugepage/defrag = madvise is the SQL-Server-friendly setting; always causes unpredictable latency spikes during compaction.

max server memory and secondary settings

  • Always set an explicit max server memory (MB). On the stoxx host, a reasonable value is 22684 (leaves about 2 GB for SQLPAL / CLR / LibOS / Agent / Full-Text inside the process). Do not leave it at the default 2147483647.
  • Set max server memory (MB) below memory.memorylimitmb. The rule of thumb is memory.memorylimitmb − 2000 MB. max server memory (MB) only caps the buffer pool and most clerks; the non-buffer-pool components need headroom between the two caps.
  • Leave min server memory (MB) at the default 0 unless running on a contested Windows host with multiple SQL Server instances.
  • Leave query wait (s) at -1 (formula-based 25× query cost). Positive overrides are almost always worse under burst load.
  • Raise min memory per query (KB) from 1024 to 4096 only when many small queries are consistently spilling sorts or hashes to tempdb and the underlying indexing or statistics fix is not feasible.

Plan cache hygiene

  • Enable optimize for ad hoc workloads on any instance where CACHESTORE_SQLCP holds significant memory and sys.dm_exec_cached_plans with usecounts = 1 AND objtype = 'Adhoc' shows non-trivial waste. On stoxx, 399 single-use ad hoc plans currently waste 53.78 MB — this is the correct remediation.
  • Favour parameterised client code. optimize for ad hoc workloads is a mitigation, not a cure — the root fix is that ad hoc plans should be parameterised at the client (prepared statements, sp_executesql, ORM parameterisation).
  • Monitor USERSTORE_TOKENPERM. This is the security-token cache. Dynamic-SQL impersonation and high connection churn with different security contexts can bloat it silently. Watch it separately from the SQL plan cache.
  • Do not run DBCC FREEPROCCACHE as a routine step. It is disruptive. Prefer DBCC FREEPROCCACHE(<plan_handle>), ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, or DBCC FLUSHPROCINDB(DB_ID('<db>')) for targeted remediation.

Buffer pool monitoring

  • Do not judge PLE right after a restart. PLE climbs linearly with time since startup until the buffer pool warms up. Revisit once the instance has been running long enough to reach steady state.
  • Read buffer cache hit ratio as raw / base, not as the raw counter alone. The raw counter is not a percentage.
  • Monitor buffer pool occupancy by database, not in aggregate. An incidental database dominating sys.dm_os_buffer_descriptors during an incident usually indicates a scan or missing-index problem in that specific database, not an instance-wide memory shortage.
  • Watch tempdb buffer pool usage. High tempdb with high dirty_pages_mb indicates active row versioning or worktable spill; correlate with sys.dm_tran_version_store and long-running transactions.

Memory grants and resource semaphores

  • Always check sys.dm_exec_query_resource_semaphores alongside sys.dm_exec_query_memory_grants. The semaphore view shows whether the pool is exhausted; the grants view shows which queries are affected.
  • Enable memory grant feedback at compat level 140+ (for batch mode) or 150+ (for row mode) on any Enterprise or Developer instance. Leave persistence and percentile enabled on SQL 2022+; they fix oscillation issues in the earlier two waves.
  • Use DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK as a per-query hint when one specific plan is being adjusted in the wrong direction. Do not disable feedback instance-wide as a workaround.
  • Track forced_grant_count on the regular resource semaphore. A nonzero and growing value means SQL Server is issuing minimum-size grants under pressure — this is a chronic memory shortage, not a one-off event.

Audit and telemetry

  • Capture DBCC MEMORYSTATUS output into a file during any OOM or low-memory incident (docker exec stoxx-db bash -c "/opt/mssql-tools18/bin/sqlcmd ... -Q 'DBCC MEMORYSTATUS WITH NO_INFOMSGS' > /tmp/dbcc_memstatus.txt"). It is the single artifact Microsoft Support asks for first.
  • Record sys.dm_os_memory_clerks snapshots on a schedule. A one-shot capture shows a moment; a time series shows whether a clerk is growing.
  • Alert on RESOURCE_SEMAPHORE wait type and RESOURCE_SEMAPHORE_SMALL_QUERY separately. The small-query wait indicates different pressure (many small queries) than the regular wait (large grants queueing).
  • Pair every memory intervention with a before/after capture. Re-run the baseline queries (sys.dm_os_sys_info, sys.dm_os_memory_clerks, sys.dm_exec_cached_plans composition) before and after any sp_configure or DBCC FREEPROCCACHE action so the effect is recorded.

SQL Server Memory and the Buffer Pool References

Graph View

Backlinks