Memory configuration (min/max server memory; OS reserved)

Section 7 — Lesson 3: Memory Configuration (min/max server memory; OS reserved)

Welcome back. If parallelism is the most misunderstood CPU topic, memory is the most common outage topic.
Many SQL Server “mystery slowdowns” are actually memory pressure—either SQL Server starving the operating system,
or the operating system starving SQL Server, or both fighting each other under load.

A job-ready DBA must be able to answer these questions confidently:
How much memory does SQL Server use? How much should it be allowed to use?
How do you protect Windows? How do you prove memory pressure with evidence?

Lesson description: You will learn how SQL Server uses memory (buffer pool, plan cache, memory grants),
what min server memory and max server memory really do, and how to reserve enough memory
for Windows (OS reserved) to avoid system instability. You will learn the most common real-world mistakes (max memory left unlimited,
setting min memory too high, ignoring other apps on the server), and you will run safe DMV scripts to capture baseline evidence
before and after a memory configuration change.

1) Why Memory Configuration Prevents Outages

SQL Server is designed to use memory aggressively because memory makes databases faster.
The buffer pool caches data pages; the plan cache stores compiled query plans; memory grants support sorts/hashes.

But Windows also needs memory for:

• the OS kernel and file system cache
• drivers, networking, security software
• backup agents, monitoring agents, and sometimes other applications
• SSIS/SSRS/SSAS components (if installed) or third-party services

When SQL Server is allowed to take too much memory, Windows starts paging, the machine becomes unstable,
and everything becomes slow. That is a classic DBA-caused outage.

DBA goal: Give SQL Server enough memory to perform well while guaranteeing the OS stays healthy.

2) The Two Settings: min server memory vs max server memory

2.1 max server memory (most important)

max server memory limits how much memory SQL Server can use for its main memory consumers (especially the buffer pool).
This is the primary knob DBAs use to keep SQL Server from starving Windows.

Common problem: On many installs, max server memory is left at a very high default (effectively “unlimited”),
which can allow SQL Server to grow until the OS is in trouble.

2.2 min server memory (often misunderstood)

min server memory is not “SQL Server will always reserve this amount at startup.”
Instead, it acts like a floor after SQL Server has grown its memory usage.
Once SQL Server has allocated memory up to or above the min setting, it tries not to release memory below that floor.

DBA takeaway: Max memory protects the OS. Min memory is about stability once SQL Server is warm,
and in many environments it is left at default unless there is a clear reason.

3) What “OS Reserved Memory” Means (The Concept DBAs Must Explain)

OS reserved memory is the amount of RAM you intentionally leave for Windows and non-SQL Server processes.
You do this by setting SQL Server max server memory lower than total installed RAM.

In production, OS reserved depends on:

• total RAM (16 GB behaves differently than 256 GB)
• what else runs on the server (backup agent, antivirus, monitoring, ETL tools)
• workload type (OLTP vs reporting; large sorts/hashes; columnstore; etc.)
• virtualization (VMs can have ballooning/host pressure)

DBA principle: If SQL Server and Windows fight for memory, users experience random timeouts and slowdowns.

4) Memory Pressure Symptoms (How It Appears)

Memory pressure in SQL Server often appears as:

• increased physical reads (less caching, more disk I/O)
• more query spills to tempdb (sort/hash spills due to poor memory grants)
• RESOURCE_SEMAPHORE waits (queries waiting for memory grants)
• recompilations or plan cache churn (less stable plan cache)
• overall slowdown that gets worse with concurrency

OS memory pressure often appears as:

• Windows paging, system sluggishness, and slow remote connections
• disk activity unrelated to SQL workload (pagefile usage)
• “everything is slow” including SSMS connections

DBA reality: SQL Server can be healthy while the OS is suffering, and the OS can be healthy while SQL Server is starving.
You must collect evidence from both perspectives.

5) Evidence First: Check Current SQL Server Memory Configuration

Run this to see min/max memory settings and confirm configured vs in-use:

SELECT
    name,
    value AS configured_value_mb,
    value_in_use AS in_use_mb,
    is_dynamic,
    description
FROM sys.configurations
WHERE name IN ('min server memory (MB)','max server memory (MB)');

Save output to: C:\DBA\Baselines\InstanceConfig\memory_config_before_YYYYMMDD_HHMM.txt

6) Evidence: How Much Memory Is SQL Server Using Right Now?

This gives you a quick view of SQL Server process memory and system memory state.

6.1 SQL Server process memory (internal view)

SELECT
    physical_memory_in_use_kb / 1024 AS sqlserver_physical_memory_mb,
    large_page_allocations_kb / 1024 AS large_page_allocations_mb,
    locked_page_allocations_kb / 1024 AS locked_page_allocations_mb,
    memory_utilization_percentage,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

Save output to: C:\DBA\Baselines\InstanceConfig\memory_process_before_YYYYMMDD_HHMM.txt

6.2 System memory state (OS-facing signal from SQL Server)

SELECT
    total_physical_memory_kb / 1024 AS total_physical_memory_mb,
    available_physical_memory_kb / 1024 AS available_physical_memory_mb,
    total_page_file_kb / 1024 AS total_page_file_mb,
    available_page_file_kb / 1024 AS available_page_file_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

Save output to: C:\DBA\Baselines\InstanceConfig\memory_system_before_YYYYMMDD_HHMM.txt

7) Evidence: Are Queries Waiting for Memory Grants?

If large sorts/hashes are occurring under pressure, queries may wait in a memory grant queue.
This is a key DBA diagnostic for “it’s slow under load.”

SELECT
    mg.session_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb / 1024 AS requested_mb,
    mg.granted_memory_kb / 1024 AS granted_mb,
    mg.used_memory_kb / 1024 AS used_mb,
    mg.max_used_memory_kb / 1024 AS max_used_mb,
    mg.wait_time_ms,
    mg.is_next_candidate,
    mg.dop
FROM sys.dm_exec_query_memory_grants AS mg
ORDER BY mg.requested_memory_kb DESC;

Save output to: C:\DBA\Baselines\InstanceConfig\memory_grants_before_YYYYMMDD_HHMM.txt

8) Setting max server memory Correctly (A DBA Method You Can Defend)

In production, DBAs typically follow this process:

1. Identify total RAM and confirm if SQL Server is dedicated or shared with other services.
2. Decide OS reserved memory (Windows + agents + overhead + safety buffer).
3. Set max server memory = total RAM − OS reserved.
4. Validate system_memory_state, paging signals, and SQL Server stability after change.
5. Document decision and rollback plan.

Lab note: In your lab, the goal is not the perfect number. The goal is the professional workflow.

9) Making the Change (Lab Example) + Verification

In your lab, choose a max memory value that leaves room for Windows.
For example, on a small VM you may leave several GB for the OS. The key is: do not set max memory to “all RAM.”

Use this pattern to change memory settings safely:

-- Show advanced options if needed
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Example: set max server memory (MB)
-- Replace 8192 with your chosen value in MB
EXEC sp_configure 'max server memory (MB)', 8192;
RECONFIGURE;

-- Optional: set min server memory (MB) only if you have a clear reason
-- In many environments, min memory is left at default unless proven necessary
-- EXEC sp_configure 'min server memory (MB)', 0;
-- RECONFIGURE;

9.1 Verify configured vs in-use

SELECT
    name,
    value AS configured_value_mb,
    value_in_use AS in_use_mb
FROM sys.configurations
WHERE name IN ('min server memory (MB)','max server memory (MB)');

Save output to: C:\DBA\Baselines\InstanceConfig\memory_config_after_YYYYMMDD_HHMM.txt

10) Validation: What Should Improve After Correct Memory Settings?

After setting max memory correctly, you are trying to prevent OS instability and reduce pressure symptoms.
DBAs validate with:

• system_memory_state_desc is not chronically “low”
• available physical memory is not near zero during normal load
• SQL Server process memory stabilizes and does not starve Windows
• memory grant waits reduce if they were caused by extreme pressure
• overall response time becomes more consistent

In your lab, run the same “before” queries again and save them as “after.”
Compare the results.

Practice (Required)

1. Capture “before” evidence:
   • memory_config_before_YYYYMMDD_HHMM.txt
   • memory_process_before_YYYYMMDD_HHMM.txt
   • memory_system_before_YYYYMMDD_HHMM.txt
   • memory_grants_before_YYYYMMDD_HHMM.txt
2. Create a change note:
   C:\DBA\Logs\Changes\change_note_memory_YYYYMMDD_HHMM.txt
   (Include rollback: return max server memory to the previous value.)
3. Set max server memory to a value that leaves OS headroom on your lab machine,
   then capture “after” evidence:
   • memory_config_after_YYYYMMDD_HHMM.txt
   • memory_process_after_YYYYMMDD_HHMM.txt
   • memory_system_after_YYYYMMDD_HHMM.txt
   • memory_grants_after_YYYYMMDD_HHMM.txt
4. In your DBA notebook, answer:
   • Why is max server memory the most important memory setting for DBAs?
   • What does min server memory actually do (and what does it NOT do)?
   • What evidence would indicate OS memory pressure?
   • What evidence would indicate memory grant pressure?

Lesson Summary

• SQL Server uses memory aggressively; DBAs must protect Windows by setting max server memory.
• Min server memory is a floor after growth, not a startup reservation.
• Memory pressure shows up as higher I/O, spills, memory grant waits, and OS instability.
• Job-ready DBAs baseline, change, validate, and document memory settings.

Next Lesson: File Growth Strategy (autogrowth sizing, instant file initialization concept)

Next, you will learn how file growth causes real outages and performance cliffs,
how DBAs choose sane growth increments, and how instant file initialization fits into safe operations.