How to set MAX_SERVER_MEMORY

  • Article

OK I'm on a roll here blogging as my brain is full up on the MCA course. Here's a good tip on how to calculate the MAX_SERVER_MEMORY

a) calculate worker threads (assuming set correctly, as default max 255 may be too low for huge beasts)

select max_workers_count from sys.dm_os_sys_info

b) Figure out thread stack size

Platform Size
32 bit 512k
x64 2MB
IA64 4MB

or

select max( stack_bytes_committed )
from sys.dm_os_threads

c) guess how much ram to leave for OS/apps and MemToLeave

2-4Gb guess

Formula:

MAX_SERVER_MEMORY= TOTAL_SERVER_MEMORY - [ Os/Apps Memory] - [Threads * Thread Size]

so an x64 with 1024 threads and 64 GB ram could have MAX_SERVER_MEMORY of 58 GB.

On the IA64 platform this would be 56 GB as ThreadSize is bigger.