The question:
Suppose I limit the SQL Server memory to 100 GB.
Does this only limit the buffer to 100 GB or does it also limit the query memory grants?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
On SQL Server 2012 and later, max server memory limits almost all memory committed by SQL Server. The buffer pool is just one cache that competes with others (like memory grants and the plan cache) for memory within that limit.
If you set max server memory to 100GB, that limits the total memory available to all potential consumers. So, no, it is not a limit on the buffer pool alone, it would limit memory usage much more generally.
Nothing is ever simple though. SQL Server can exceed the max server memory limit (over-commitment) for short periods of time if a multi-page allocation requires contiguous memory that is not currently available.
This behavior is typically observed during the following operations:
- Large Columnstore index queries.
- Large Batch Mode on Rowstore queries.
- Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.
- Backup operations that require large memory buffers.
- Tracing operations that have to store large input parameters.
See Memory Management Architecture Guide and Server memory configuration options in the documentation. The latter contains the following advice:
Use max_server_memory to guarantee the OS does not experience detrimental memory pressure. To set max server memory configuration, monitor overall consumption of the SQL Server process in order to determine memory requirements. For an initial configuration or when there was no opportunity to collect SQL Server process memory usage over time, use the following generalized best practice approach to configure max_server_memory for a single instance:
- From the total OS memory, subtract the equivalent of potential SQL Server thread memory allocations outside the max server memory control, which is comprised of stack size1 * calculated max worker threads 2.
- Then subtract 25% for other memory allocations outside the max server memory control, such as backup buffers, extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers. This is a generic approximation, mileage may vary.
- What remains should be the max_server_memory setting for a single instance setup.
1 Refer to the Memory Management Architecture guide for information on thread stack sizes per architecture.
2 Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0