February 10, 2012 at 2:44 am
When calculating the current memory usage in order to set the max memory settings, should I multiply the counter values of ReservedPages & StolenPages by 8 to get the values in KB ?? Or are they already provided in KB ?
Here, on msdn it says that these counter represent the number of 8k pages but it doesn't multiply by 8 to get the values in KB when calculating the exact usage:
From MSDN:
Before reducing the max server memory value, use Performance Monitor to examine the SQLServer:Buffer Manager performance object while under a load, and note the current values of the Stolen pages and Reserved pages counters. These counters report memory as the number of 8K pages. max server memory should be set above the sum of these two values to avoid out-of-memory errors. An approximate value for the lowest reasonable max server memory setting (in MB) is ([Stolen pages] + [Reserved pages])/ 100.
February 10, 2012 at 3:16 am
Workshops say your server needs 1 GB for server processes. So mostly i do this.
16383 mb (it is in megabytes). 16 gig
2048 mb. 2 gig
16384 - 2048 = 14336 is max mem setting sql.
Note: Max server settings is for the memory usage of an instance. Other sql processes can use more memory, that why we advise the 2 gig.
After this setting you can fine tune a bit + 1024 or + 2048 or - 1024 or - 2048. Fine tune till your server has 1 gig free for operations. You can fine tune this setting online and don't forget other applications on a server. They use memory too.
See official Microsoft documentation in the attached file if you want to calculate it.
Kind regards,
André Borgeld
February 10, 2012 at 4:03 am
a.borgeld (2/10/2012)
Workshops say your server needs 1 GB for server processes. So mostly i do this.16383 mb (it is in megabytes). 16 gig
2048 mb. 2 gig
16384 - 2048 = 14336 is max mem setting sql.
Note: Max server settings is for the memory usage of an instance. Other sql processes can use more memory, that why we advise the 2 gig.
After this setting you can fine tune a bit + 1024 or + 2048 or - 1024 or - 2048. Fine tune till your server has 1 gig free for operations. You can fine tune this setting online and don't forget other applications on a server. They use memory too.
See official Microsoft documentation in the attached file if you want to calculate it.
Kind regards,
André Borgeld
Thanks Andre for your suggestion. If it was a single Instance server this approach works fine. However, I am asking this in the context of a server which hosts more than 1 instance on it, say 4 or more. In that case we should analyze the memory required by each instance first because we have to distribute the physical memory available to the server among the instances.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply