From: esquel@sommarskog.se   
      
   Anton Shepelev (anton.txt@g{oogle}mail.com) writes:   
   > As you see from the Overrun column, most of the time actual   
   > memory usage exceeds the configured limit by 31-33   
   > megabytes, occasionally coinciding with it, at which moments   
   > an interesting anomaly takes place: a higher memory limit   
   > results in a futher decrease of used memory, for example: a   
   > limit of 857 Mb caused memory usage to drop to 888 Mb, but   
   > then a higher limit of 887 megabytes decreased usage to the   
   > same 887 megabytes!   
   >   
   > Is this behavior ducumented anywhere, and is there a method   
   > of stable and predictable control of used memory?  I can   
   > think of setting the limit to at least 34 megabytes less   
   > than current usage, but this may depend on MSSQL version and   
   > environment...   
   >   
      
   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.   
      
   As for the usage decreasing when you are increasing the memory, it can be   
   because the OS is signaling memory pressure, or because SQL Server is still   
   working with trimming the memory. The memory will not start to increase   
   until there are queries that needs to drag pages into memory.   
      
   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.)   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)