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: fsxNet Usenet Gateway (21:1/5)