Using Dynamic Memory Management

All replies

• 

  

  0

  Sign in to vote

  If you are looking for max and min memory configuration, you can use the below:

  sp_configure --To know all configurations
  
  
  sp_configure 'max server memory (MB)' -- max memory
  sp_configure 'min server memory (MB)' -- min memory

  
  

  Ref: 

  http://msdn.microsoft.com/en-IN/library/ms188787.aspx

  Tuesday, May 27, 2014 6:00 AM

  Answerer
• 

  

  0

  Sign in to vote

  Hello Kalyan,

  Yes, by Default dynamic Memory Management is enabled. If you open the Server property in SSMS => Memory you will see Min mem = 0 and max mem = 2 PB

  

  ---

  Olaf Helper

  [ Blog] [ Xing] [ MVP]

  Tuesday, May 27, 2014 6:04 AM
• 

  

  0

  Sign in to vote

  What specific do you want to know if is enable, Are you asking about "AWE mechanism"? If so it is configure using the sp_configure.

  -- showe and configure advance options:
  EXECUTE sp_configure 'show advanced options', 1;
  GO
  RECONFIGURE;
  GO
  
  -- Now we can check if awe enabled:
  EXECUTE sp_configure 'awe enabled'
  GO
  
  -- and enable it if needed
  sp_configure 'awe enabled', 1
  RECONFIGURE
  GO

  ---

  [Personal Site] [Blog] [Facebook]

  Tuesday, May 27, 2014 6:06 AM
• 

  

  0

  Sign in to vote

  By the way this will work on old servers only. SQL 2012 Deprecated Features-32-bit systems - AWE

  ---

  [Personal Site] [Blog] [Facebook]

  Tuesday, May 27, 2014 6:09 AM
• 

  

  0

  Sign in to vote

  Is this by default enabled in the server or else Please suggest on how to achieve this

  Regards,

  Kalyan

  ---

  Grateful to your time and support. Regards, Shiva

  
  

  If you read the first line of the link you posted its clearly written SQL Server memory management is dynamic. You cannot change it . You can just restrict buffer pool usage by puttling limit on MAX Server memory. In that case as well SQL Server might cache all Buffer pool memory.

  Event though you cap max server memory still direct memory allocations for large pages will be done directly by windows API and that will also come under SQL Server memory utilization

  In short SQL server memory allocation is always dynamic you cannot change it

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.
  
  My TechNet Wiki Articles

  
  

  • Edited by Shanky_621MVP Tuesday, May 27, 2014 8:25 AM
  • Proposed as answer by Kalman Toth Wednesday, May 28, 2014 11:42 AM
  • Marked as answer by tracycai Friday, May 30, 2014 7:32 AM

  Tuesday, May 27, 2014 8:24 AM