I'm trying to come up with a sane way to understand if the
max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that
max server memory (mb) should always be low enough to leave room for the operating system itself, etc.
The environment I'm looking at has several hundred servers; I need a reliable formula I can use to determine if the present size of the buffer pool is appropriate since RAM is costed per GB allocated to each server. The entire environment is virtualized, and "physical" RAM allocated to a VM can easily be changed up or down.
I have a particular SQL Server Instance I'm looking at now with a PLE of 1,100,052 seconds, which equates to 12.7 days (the amount of time the server has been up). The server has a max server memory setting of 2560MB (2.5GB), of which only 1380MB (1.3GB) is actually committed.
I've read several items including one by Jonathan Keheyias (post) and another by Paul Randal (post), and several others. Jonathan advocates monitoring for a PLE below 300 per 4GB of buffer pool as being too low. For the SQL Server Instance above,
300 * (2.5 / 4) = 187 results in a really really low target PLE below 300. This instance has 290GB of SQL Server data (not including log files), and is only used for integration testing. Assuming the last 12 days is representative of typical usage for this server, I'd say the
max server memory (mb) setting could be lowered.
At the other end of the scale, I have another integration-test server with a PLE of 294, that has a
max server memory (mb) setting of only 1GB. This server has only 224MB of SQL Server data not including logs, and is running some BizFlow databases. This server might benefit from a higher
max server memory (mb) setting.
I'm thinking a good starting place for targets that might be assigned too much memory could include looking at:
SELECT RamMB = physical_memory_in_bytes / 1048576 , BufferPoolCommittedMB = bpool_committed * 8192E0 / 1048576 , BufferPoolCommitTargetMB = bpool_commit_target * 8192E0 / 1048576 , PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),bpool_committed) / bpool_commit_target) * 100) FROM sys.dm_os_sys_info;
BufferPoolCommitTargetMB / BufferPoolCommittedMB is greater than 1, the server is not using the entire buffer pool. If the machine in question also has a PLE of greater than "x" then it might be a good candidate for a decrease in
max server memory (mb).
Buffer Manager:Lazy writes/sec performance counter tracks the number of times SQLOS has written pages out to disk between checkpoints due to memory pressure, this might be another good thing to look at.
DECLARE @WaitTime DATETIME; SET @WaitTime = '00:00:15'; DECLARE @NumSeconds INT; SET @NumSeconds = DATEDIFF(SECOND, 0, @WaitTime); DECLARE @LazyWrites1 BIGINT; DECLARE @LazyWrites2 BIGINT; SELECT @LazyWrites1 = cntr_value FROM sys.dm_os_performance_counters dopc WHERE ( dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS ) AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255), SERVERPROPERTY('InstanceName')) + ':Buffer Manager'; WAITFOR DELAY @WaitTime; SELECT @LazyWrites2 = cntr_value FROM sys.dm_os_performance_counters dopc WHERE ( dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS ) AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255), SERVERPROPERTY('InstanceName')) + ':Buffer Manager'; SELECT LazyWritesPerSecond = (@LazyWrites2 - @LazyWrites1) / @NumSeconds;
The above code assumes the server is under load during the 15 seconds it takes to run, otherwise it will report 0; which might be a misleading false-negative.
Should I also look at
PAGELATCHIO_* wait stats or some other wait type as an indicator of memory pressure, or lack thereof?
My question is, how can I reliably determine a "good" target value for PLE and
max server memory (mb)?