All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂