max server memory setting.

Question

hi Folks,

On our sql server "max server memory" is set to more than the physical memory. Physical memory is 250G while max server memory is set to 1TB. This was done as per recommendation from one of the outside consultant. This consultant suggested to increase this value because BPE is enabled on this server and BPE file size is around 1TB. Server was running fine for few months, but of late im seeing lot of slowness on the server. Is there any memory related values i can check to make sure this is not happening because of max server setting value ? What perfmon/sql server values i can see to make sure this high value of max server memory setting is not impacting the performance.

Answer 1

What the consultant said is entirely incorrect. BPE file has NOTHING to do with Max Server Memory. It does not have any interaction with Max Server Memory.

See:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/buffer-pool-extension?view=sql-server-ver15#capacity-limitations

There are many variables to take into consideration when setting Max Server Memory. However, it should never be greater than physical RAM minus some space for the OS and other processes to run.

What is the result of SELECT @@VERSION

Do you have "Lock Pages in Memory" set?

Do you see "A significant part of SQL Server process memory has been paged out." in the SQL Server error log?

Is the server dedicated to SQL Server Database Service usage? Are there other processes running, including SSIS, SSRS, etc?

Answer 2

The most likely reason that you see slowness is that you have queries that needs tuning. If you have not enabled Query Store for the database on the servers do. If you have enabled, dig in and find the slow queries.

Answer 3

Hi @Heisenberg ,

Your max server memory setting is incorrect.

Quote from MS document Server memory configuration options;

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 (MB) for a single instance:

• From the total OS memory, subtract the equivalent of potential SQL Server thread memory allocations outside max server memory (MB) control, which is the stack size1 multiplied by calculated max worker threads2.
• Then subtract 25% for other memory allocations outside max server memory (MB) control, such as backup buffers, extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), allocations from linked server providers, columnstore indexes, and allocations for in-memory objects. This is a generic approximation, and your mileage may vary.
• What remains should be the max server memory (MB) setting for a single instance setup.

Setting max server memory (MB) value too high can cause a single instance of SQL Server to compete for memory with other SQL Server instances hosted on the same host.

And max server memory setting is not related to BPE. Set the buffer pool extension so the ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Physical memory is 250G while max server memory is set to 1TB.

That setting don't make any sense, but at the end it doesn't matter much. Beside you should spend some mem for the OS; a better max. mem setting would be 242 GB => 8 GB left for OS

This consultant suggested to

If a consultant suggest such, the he isn't the money worth. Get an other one.

Answer 5

Thank you all for the valuable inputs, As @Erland Sommarskog said there is definitely need for query tuning, i see lot of badly performing queries through SQLSentry (its a great tool). Besides this what performance monitoring parameter or dynamic view values i can check to determine SQL Server is under memory pressure.? buffer cache hit ratio seems to be a bogus parameter it always shows 99%. PLE is is bit low its around 100. Anything else i can see?