About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
SQL Server is designed to use memory. Left at its defaults, it will keep taking memory as its workload grows, up to a ceiling that is in practice unlimited. On a dedicated physical server with nothing else on it, that is sometimes tolerable. On a hosted virtual machine that also runs Windows services, monitoring agents and perhaps the business application itself, it can leave too little memory for everything else.
This article explains the two memory settings that matter most, how Microsoft suggests calculating the cap, what is different on a virtual machine, and how to decide about the Lock Pages in Memory right.
Why it matters
When SQL Server holds too much memory, Windows and other programs on the same VM run short. Symptoms can include a slow server console, failing agents, or Windows paging other programs to disk. When SQL Server holds too little, queries read more from disk than they need to and the application slows.
Microsoft's guidance is clear that the default should be changed: It's recommended in all versions of SQL Server to configure an upper limit for SQL Server memory utilization by setting max server memory.
How it works
Two settings bound SQL Server's memory
Microsoft describes SQL Server's memory as bounded by a pair of configuration settings, min server memory (MB) and max server memory (MB). The defaults are 0 for min server memory and 2,147,483,647 MB for max server memory; in practice, no cap.
Since SQL Server 2019, setup on Windows provides a recommendation for the max server memory (MB) for a standalone SQL Server instance based on a percentage of available system memory at the time of installation. That recommendation reflects the VM as it was at installation. If memory was added or removed later, or other software was installed, it may no longer fit.
The cap does not cover everything SQL Server uses
The cap does not cover the entire SQL Server process. The short configuration page describes a buffer-pool limit, but Microsoft's more detailed memory architecture guide explains that, since SQL Server 2012, single-page, multi-page and CLR allocations handled by the SQL Server memory manager are included. The cap therefore covers the buffer pool and other managed allocations and caches. Thread stacks and direct Windows allocations, including some extension and linked-server allocations, remain outside it. Leave room for those as well as Windows and other programs.
Microsoft's starting calculation
Where you have not yet measured the server's real usage, Microsoft gives a generalised starting method for a single instance:
- From the total memory, subtract the memory for worker thread stacks: the thread stack size multiplied by the calculated maximum number of worker threads.
- Then subtract 25% for other memory allocations outside max server memory (MB) control, such as backup buffers and linked server providers.
- What remains is the starting value for max server memory.
Microsoft describes this as a generic approximation, and your mileage might vary.
On 64-bit SQL Server, the thread stack size is 2,048 KB, or 2 MB. On a VM with up to four logical CPUs, the default maximum is 512 worker threads, for SQL Server 2016 SP2 and later. Above four logical CPUs (up to 64), the default grows by 16 threads per extra CPU, as listed on the max worker threads page.
A worked example
An illustration only, for a VM with 16 GB of memory and four virtual CPUs, running only SQL Server and Windows:
| Step | Value |
|---|---|
| Total memory | 16,384 MB |
| Worker thread stacks: 512 × 2 MB | − 1,024 MB |
| Remaining | 15,360 MB |
| Less 25% for other allocations | − 3,840 MB |
| Starting max server memory | about 11,520 MB |
Microsoft's wording does not state whether the 25% applies to the total or to what remains after step 1; this example applies it to what remains. Either reading gives a starting point, not a final answer. If the business application, a reporting tool or other agents also run on the VM, subtract their measured memory first.
Then measure and adjust
Microsoft's preferred approach is measurement: monitor overall memory consumption of the server hosting the SQL Server instance, during normal operation before settling the value. Watch available memory in Windows during the busiest periods, such as month-end, and adjust.
Changes take effect without a restart, but care is needed: setting max server memory far too low can even prevent SQL Server from starting.
Edition limits
Some editions cap the buffer pool regardless of the setting. For SQL Server 2025, Microsoft lists a buffer pool maximum of 256 GB for Standard and 1,410 MB for Express; for SQL Server 2022 and earlier, Standard supports up to 128 GB. Giving an Express instance more memory than its limit does not make the buffer pool larger.
What is different on a virtual machine
Min server memory
On a physical server, min server memory is often left at 0. Microsoft treats VMs differently: Setting a min server memory (MB) value is essential in a virtualized environment to ensure memory pressure from the underlying host doesn't attempt to deallocate memory from the buffer pool on a guest virtual machine (VM) beyond what is needed for acceptable performance.
SQL Server does not allocate the minimum at startup. Demand must grow the managed allocation to that level first. The architecture guide explains that, once reached, the minimum is a floor: SQL Server can still release memory above it when the operating system is short of memory. It does not mean every subsequent allocation is retained forever. Avoid setting min and max to the same or nearly the same value.
Ask what the host does with memory
Whether the host can reclaim memory from your VM depends on the hypervisor and the provider's settings. Ask your provider whether your VM's memory is reserved or can be reclaimed when the host is busy. If it is fully reserved, host-side pressure on your VM is less of a concern, but the in-guest cap still matters for Windows and other programs.
Lock Pages in Memory: when and when not
Lock Pages in Memory (LPIM) is a Windows user right. In Standard edition and higher, it allows SQL Server to keep pages allocated through the locked-page mechanism, using Windows AWE APIs, out of paging. It does not make every allocation in the SQL Server process nonpageable. Microsoft says this might keep the server responsive when paging memory to disk occurs.
Microsoft ties its use to evidence: LPIM should be used when there are signs that the sqlservr process is being paged out. The sign it names is error 17890 in the SQL Server error log, reporting that a significant part of the process memory has been paged out.
It also carries a risk. Using LPIM with a max server memory value that doesn't account for other memory consumers in the system might cause instability. If you grant LPIM, set max server memory to a specific value first.
If you do grant it, Microsoft recommends assigning the right to the SQL Server service's own security identifier, which ensures that the grant remains even if you change the service account of the Database Engine service. The instance must be restarted for the right to take effect.
Checking the current state
Microsoft documents read-only Transact-SQL queries for checking both settings, run in a query window in SQL Server Management Studio or another SQL client connected to the instance. The memory model of the running instance is shown in the sql_memory_model_desc column of sys.dm_os_sys_info: CONVENTIONAL means LPIM is not granted, and LOCK_PAGES means it is. Microsoft also documents LARGE_PAGES for a separate advanced configuration; these are not just two possible states. The current memory settings are listed in sys.configurations. See the Server memory configuration options page for the exact queries.
What to choose and why
- Set max server memory deliberately on every instance. Start from Microsoft's calculation, subtract what else runs on the VM, then measure during busy periods.
- Set a modest min server memory on a VM if the host can reclaim memory, well below max server memory.
- Grant LPIM only with a reason, such as error 17890 entries, and only after max server memory is set correctly.
- Revisit both settings whenever the VM's memory changes. A resize at the provider does not change SQL Server's settings.
- Record the values and why. The next person to resize the VM needs to know.
Risks and limits
- The calculation is a starting point, not a measurement. Microsoft calls it an approximation.
- Test changes outside production first. Use an isolated copy with synthetic or masked data and a representative workload, and confirm the instance starts and performs as expected before changing production.
- Several instances on one VM need a shared plan. Their caps together must not exceed the memory available.
- Some workloads use memory outside the cap, such as linked servers and some extensions; measure rather than assume.
- This article does not cover Linux. SQL Server on Linux uses a different setting.
- No hypervisor-specific settings are given. Memory reservation and reclamation depend on the provider's platform.
Sources
Opened 14 November 2026.
- Microsoft Learn: Server memory configuration options.
- Microsoft Learn: Enable the Lock Pages in Memory option (Windows).
- Microsoft Learn: Server configuration: max worker threads.
- Microsoft Learn: Memory management architecture guide.
- Microsoft Learn: Editions and supported features of SQL Server 2025.
Related service: Cloud hosting.
Related future topics
- MAXDOP and cost threshold for parallelism on hosted SQL Server.
- Reading SQL Server memory pressure signals.
- Sizing a business application server from measurements.
Learning notes
- The default max server memory is effectively unlimited; Microsoft recommends setting a cap.
- The cap covers SQL Server memory-manager allocations, including the buffer pool; thread stacks and direct Windows allocations still need headroom.
- Microsoft's starting method: subtract thread stacks, then 25%, then measure.
- On a VM, Microsoft calls a min server memory value essential where the host can reclaim memory.
- Grant Lock Pages in Memory when paging of SQL Server is evident, and only with a specific cap.
This article was originally published on .