From: anton.txt@g{oogle}mail.com   
      
   Erland Sommarskog:   
      
   > Max server memory main controls the buffer cache, which   
   > also is the main consumer. But there are also memory   
   > allocations that are outside "max server memory", so it is   
   > not unusual for the actual usage to exceeds the setting a   
   > little bit.   
      
   The documentation seems to disagree with you:   
      
      Reconfigure the amount of memory (in megabytes) for a SQL   
      Server process used by an instance of SQL Server.   
      
   When I set up a limit of 768 Mb, restarted the instance, and   
   put some high memory pressure on it, the actual usage never   
   exceeded 768 Mb:   
      
      sys.dm_os_process_memory: physical_memory_in_use_kb/1024 = 751   
      Task Manager            : Working set (memory)     /1024 = 751   
      
   I then increased the limit to 1024 Mb and imparted more   
   memory pressure on the instance.  The memory usage was again   
   below the limit:   
      
      sys.dm_os_process_memory: physical_memory_in_use_kb/1024 = 1015   
      Task Manager            : Working set (memory)     /1024 = 1015   
      
   As you see, SQL Server does not exceed its memory limit if   
   it is not exceeded already (which it is when decreasing the   
   limit below actual usage)!   
      
   > Don't make this too dynamic. I don't know for sure, but I   
   > would assume that max server memory are one of the options   
   > that clear the plan cache, and thus causes a lot of   
   > recompilations. (Because the amount of available memory   
   > affects compilation of queries.)   
      
   My script is executed every hour and causes the memory usage   
   of an idle SQL Server to drop by a factor of two closer to   
   the base level (128 Mb) during each working day. In that   
   sense, it is not very dynamnic.  The source is far from   
   publishing quality, but you are welcome to exemine it if   
   intersted:   
      
   sp_configure 'show advanced options', 1 RECONFIGURE   
      
   DECLARE @srv_n INT =    6 -- number of MSSQL instances   
   DECLARE @dec_2 INT =   10 -- number of iterations for half-decay   
   DECLARE @m_res INT = 2048 -- memory reserved for the OS   
      
   -- TODO: instead of c_mis, try: round down to next 32, subract 32.   
   DECLARE @c_mmi INT         = 128   
   DECLARE @c_mma INT         = 2147483647   
   DECLARE @c_ste INT         = 32 -- minumum memory decrement (exp.)   
   DECLARE @c_two FLOAT       = 2   
   DECLARE @c_min VARCHAR(17) = 'min server memory'   
   DECLARE @c_max VARCHAR(17) = 'max server memory'   
      
   DECLARE @v_low BIT,   
           @p_low BIT   
   -- RAM values, in MB:   
   DECLARE @m_min INT        -- min RAM parameter   
   DECLARE @m_use INT        -- RAM used by this MSSQL instance   
   DECLARE @m_lim INT        -- target RAM limitation   
   DECLARE @max_l INT        -- max limit   
   DECLARE @N     INT   
      
   -- Active only in work hours:   
   IF NOT DATEPART( hour, GETDATE() ) BETWEEN 9 AND 19 GOTO Finish   
      
   SELECT @m_use  = physical_memory_in_use_kb/1024,   
          @p_low  = process_physical_memory_low   ,   
          @v_low  = process_virtual_memory_low   
     FROM sys.dm_os_process_memory   
       IF @v_low=1 OR @p_low=1 GOTO Finish   
      SET @m_min  = @c_mmi   
       IF @m_use <= @m_min     GOTO Finish   
      SET @m_lim  = @m_min + (@m_use - @m_min) / POWER(@c_two, 1.0/@dec_2)   
      SET @max_l  = @m_use - @m_use % @c_ste - 1 - @c_ste + 8   
       IF @max_l  < @m_lim SET @m_lim = @max_l   
       IF @m_lim  < @m_min SET @m_lim = @m_min   
      
   PRINT FORMATMESSAGE('Decreasing memory use from %i to %i', @m_use, @m_lim)   
      
      EXEC sp_configure @c_min, 0   
      EXEC sp_configure @c_max, @m_lim RECONFIGURE   
      
   SET @N = 12   
   WHILE @N > 0   
   BEGIN   
      WAITFOR DELAY '00:00:05'   
      SELECT @m_use = physical_memory_in_use_kb/1024,   
             @p_low = process_physical_memory_low   
      FROM sys.dm_os_process_memory   
      IF  @p_low = 1               BREAK   
      IF  @m_use < @m_lim + @c_ste BREAK   
      SET @N     = @N     - 1   
   END   
      
      EXEC sp_configure @c_max, @c_mma RECONFIGURE   
      
   Finish:   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)