Memory (Min and Max) Question

• dhechle

  SSC Eights!

  Points: 922

  More actions

  March 28, 2006 at 3:14 am

  #113348

  Hi,

  I have a question regarding the allocation of memory for SQL. I have been reading in BOL regarding setting memory options manually and came across a paragraph that says the following:

  In the first method, set min server memory and max server memory to the same value. This value corresponds to the fixed amount of memory to allocate to SQL Server

  Is this done in the Dynamically configure SQL Server memory section of the properties of the server where you would set both min and max to the same value or is it by setting a value in the Use a fixed memory size.

  The reason i ask is our SQL server has been set to use a fixed memory size of 6Gb (also using the PAE and 3GB switch) but the Reserve physical memory for SQL Server which i believe is the same as the setting the working set option to 1 with sp_configure has not been done. I was under the impression that if SQL dynamically configure's memory then leave the working set off as default but if you use a fixed memory size you should set it to 1.

  Any feedback on this would be much appreciated as i find this a bit of a black-art.

  thanks in advance.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  March 28, 2006 at 7:56 am

  #629172

  Based on BOL, you DO NOT set working set size when SQL is dynamically managing memory. BOL doesn't seem to detail much on this, but this (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx) says to set working set to 0.