What’s the issue?
The minimum value establishes a floor that SQL Server will not release memory below once it has been acquired. The max server memory setting controls the upper limit on memory SQL Server can allocate to its buffer pool and most internal caches. By default, this value is 2,147,483,647 MB (effectively unlimited), meaning SQL Server will consume as much memory as it needs and rarely release it back to Windows.Why is this a problem?
When SQL Server is allowed to grow without bounds, it can starve the operating system and other processes of memory. Windows may then resort to paging, where memory pages are written to disk, causing severe performance degradation across the entire server. SQL Server itself may experience instability if Windows signals memory pressure too aggressively. Even on dedicated SQL Server hosts, the operating system, antivirus, monitoring agents, and backup tools all need memory reserved for them.When minimum and maximum are set to the same value, SQL Server can no longer dynamically free or acquire memory for the buffer pool once the limit is reached. This eliminates the memory manager’s ability to respond to pressure from the operating system or other applications, which can cause plan cache instability, problems with query memory grants, and unpredictable behavior under load.
When minimum is set above maximum, the configuration is invalid and SQL Server may refuse to start or behave unexpectedly.
What should you do about this?
Check the current value by querying sys.configurations where name equals ‘max server memory (MB)’. We trecommend setting min server memory to 0, which is the default and allows SQL Server full flexibility to manage memory dynamically while still respecting the maximum.
Microsoft now recommends setting max server memory to 75 percent of total system memory for a standalone instance, leaving the remainder for the operating system and other services. For servers with very large amounts of RAM, leave more for the operating system (commonly 10 percent or more, and a minimum of 8 GB but maybe more for other applications including antivirus software). Change the value using EXEC sp_configure ‘max server memory (MB)’,
Monitor memory usage and wait statistics after the change to confirm the new setting is appropriate.
Read more…
SQL Server Health Check Worst Offenders – SQL Server Consulting – Straight Path Solutions