Target Server Memory vs Total: Is SQL Server Still Growing

Target server memory is how much memory SQL Server wants to use, and total server memory is how much it has actually taken. A gap between the two is not automatically a problem. Read both with the start time and your memory settings before you call the server starved.

The question a junior DBA asked me

A teammate pinged me on a quiet afternoon. “The server has 16 GB, SQL Server is using about 1 GB, and something is wrong, right?” I get this message often. The honest answer is “maybe not, let’s look.”

SQL Server does not grab all its memory at startup. It starts small and grows as it needs pages for data and plans. So a low number is often just a server that has not needed more yet. A restart makes it look this way for a while.

Two counters tell the story. Target is the goal under current conditions. Total is what has been committed so far. The first query puts both in megabytes with the gap and the percentage.

SELECT MAX(CASE WHEN RTRIM(counter_name) = 'Target Server Memory (KB)' THEN cntr_value ELSE 0 END) / 1024 AS TargetMB,
       MAX(CASE WHEN RTRIM(counter_name) = 'Total Server Memory (KB)'  THEN cntr_value ELSE 0 END) / 1024 AS TotalMB,
       (MAX(CASE WHEN RTRIM(counter_name) = 'Target Server Memory (KB)' THEN cntr_value ELSE 0 END)
      - MAX(CASE WHEN RTRIM(counter_name) = 'Total Server Memory (KB)'  THEN cntr_value ELSE 0 END)) / 1024 AS GapMB,
       100.0 * MAX(CASE WHEN RTRIM(counter_name) = 'Total Server Memory (KB)' THEN cntr_value ELSE 0 END)
             / MAX(CASE WHEN RTRIM(counter_name) = 'Target Server Memory (KB)' THEN cntr_value ELSE 0 END) AS TotalPercentOfTarget
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%:Memory Manager'
  AND RTRIM(counter_name) IN ('Target Server Memory (KB)', 'Total Server Memory (KB)');

You get one row. On my test instance, total is well under target, so there is a large gap. Your numbers will differ. A gap by itself says “room to grow”, not “starved”.

Check the start time before you worry

The next question is how long the server has been running. A server that restarted this morning has had little time to fill its cache. The second query shows the start time and the hours since.

SELECT sqlserver_start_time,
       DATEDIFF(hour, sqlserver_start_time, SYSDATETIME()) AS HoursSinceStart
FROM sys.dm_os_sys_info;

If the hours are small, give the server time and look again. If it has been up for days and the gap is still big, the workload simply may not need more. Both are fine outcomes.

Process memory is a different measurement

The process counts memory that the buffer counters do not, so the numbers will not match. Do not force them to. What matters here are the two flag columns. A 1 in either one means the process itself reported low memory. Zero means it did not.

SELECT physical_memory_in_use_kb / 1024 AS ProcessPhysicalMB,
       process_physical_memory_low,
       process_virtual_memory_low
FROM sys.dm_os_process_memory;

On my instance both flags are zero. That is the answer you hope for, and it is what makes a gap look harmless.

Know your own limits

Max server memory is the setting that holds Target down. If it is still at the default, SQL Server may try to use everything the operating system will give it. Min server memory is a floor, not a reservation. Check both before you change anything.

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)')
ORDER BY name;

My instance shows 2147483647 for max server memory. That number means “no limit”, the unchanged default. If yours looks the same, a production box should have a real value that leaves room for Windows and other services.

Watch it over time

One reading is a photograph. A few readings are a short film. This loop takes three samples two seconds apart. On a busy server you would sample for hours and chart it. In my run both numbers drifted by a little, and nothing dramatic happened. On a quiet test instance, expect small changes. Notice that Target is not fixed either.

DROP TABLE IF EXISTS #MemorySamples;
CREATE TABLE #MemorySamples (SampleNo int, TargetMB bigint, TotalMB bigint);

DECLARE @n int = 1;
WHILE @n <= 3
BEGIN
    INSERT #MemorySamples
    SELECT @n,
           MAX(CASE WHEN RTRIM(counter_name) = 'Target Server Memory (KB)' THEN cntr_value ELSE 0 END) / 1024,
           MAX(CASE WHEN RTRIM(counter_name) = 'Total Server Memory (KB)'  THEN cntr_value ELSE 0 END) / 1024
    FROM sys.dm_os_performance_counters
    WHERE RTRIM(object_name) LIKE '%:Memory Manager';
    SET @n += 1;
    IF @n <= 3 WAITFOR DELAY '00:00:02';
END;

SELECT SampleNo, TargetMB, TotalMB FROM #MemorySamples ORDER BY SampleNo;

DROP TABLE #MemorySamples;

Real trouble looks different. Total sits at target for a long time, and the user complaints line up with it. Add page life expectancy and disk read waits before you buy RAM. A gap is only a clue.

Next time someone worries about a low number, check the start time first.

A memory gap is not a memory problem, it is room that has not been needed yet.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.