Problem
Performance monitor has these two performance counters: SQL Server: Memory Manager\Total Server Memory (KB) and SQL Server: Memory Manager\Target Server Memory (KB). What is the difference between these two counters and does the Target Memory (KB) correlate to SQL Server maximum memory setting?
Solution
Target Server Memory (KB) is the amount of memory that SQL Server can potentially allocate to the buffer pool under its current workload. The Target Memory (KB) counter would reflect SQL Server “max server memory (MB)” when it is set as that would be the amount of memory that SQL Server could and would like to reach.
Total Server Memory (KB) is the current buffer pool size that SQL Server has allocated to itself. This performance counter indicates the operational used memory at that point in time.
To understand the two counters and SQL Server maximum memory setting, this tip provides five different scenarios in a test environment as shown below.
Test SQL Server Environment
Our test environment is a virtual machine with 6 GB of physical memory hosting SQL Server 2016 Developer Edition (SP2) on Microsoft Windows Server 2012 R2 Data Center Edition. Performance monitor is started and running in the background to capture the two performance counters SQL Server: Memory Manager\Target Server Memory (KB) and SQL Server: Memory Manager\Total Server Memory (KB) every second.
The Lock Pages in Memory privilege is not granted to the SQL Server service account, and the Windows Operating System has plenty of available physical memory throughout our testing.
In this tip, the five scenarios are marked by the five red circles as shown in the screenshot below. Each red circle represents an action performed at that point in time, and we have a corresponding section in the article for each to explain the changes and SQL Server behavior at that point in time.
Point-in-time 1: Target Memory > Total Memory after server restart
The virtual machine was restarted prior to beginning our test. SQL Server minimum and maximum memory is left at its installation default values, the minimum memory is left at 0 MB, and the maximum memory is at its default value 2,147,483,647 MB.
- At point-in-time 1, the SQL Server service was started and this is the sharp dip to zero observed for both Target Memory (KB) and Total Memory (KB) counter. The SQL Server process releases all its memory just like any normal application behaviour.
- SQL Server default maximum memory value of 2,147,483,647 MB means no maximum memory value is configured and SQL Server will allocate memory dynamically. This high value does not mean SQL Server will try to reach this as its target memory or try to allocate this much memory to itself.
- We can see the Target Memory (KB) counter is at 4.8 GB before and after the SQL Server service was restarted. This indicates SQL Server memory manager can set a reasonable target memory under its default configuration.
- The Total Memory (KB) shows SQL Server has allocated itself approximately 230 MB.
- Target Memory > Total Memory behavior is normal after restart, this is not an indication of SQL Server memory pressure at all.
Point-in-time 2: Setting SQL Server Minimum and Maximum Memory
SQL Server minimum memory is set to 1 GB, and its maximum memory is set to 3 GB.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'min server memory (MB)', N'1024'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3072'
GO
RECONFIGURE WITH OVERRIDE;
GO
- As soon as SQL Server maximum memory was set to 3 GB, the Target Memory (KB) reflects this value immediately.
- When SQL Server minimum memory was set to 1 GB, the Total Memory (KB) does not immediately ramp up to 1 GB.
- SQL Server memory manager does not vigorously ramp up its memory usage to the configured minimum memory value on startup if it is idle. This is evident by looking at the flat Total Memory (KB), blue line, which represents the memory that SQL Server allocates to itself.
Point-in-time 3: Executing SQL Workload
We will first create a database to run our workload.
USE [master]
GO
CREATE DATABASE [RampUpBufferPool]
GO
ALTER DATABASE [RampUpBufferPool] MODIFY FILE ( NAME = N' RampUpBufferPool', SIZE = 3GB )
GO
ALTER DATABASE [RampUpBufferPool] MODIFY FILE ( NAME = N' RampUpBufferPool_log', SIZE = 1GB )
GO
We will run the batch script below to force SQL Server to ramp up its buffer pool allocation. The principle is simple, each row insertion in the script below will occupy one 8K data page into the SQL Server buffer pool. We will insert 10,000 rows in a loop into a table, then truncate the table and repeat.
To expedite the buffer pool allocation, we will execute the batch script concurrently in four separate Query Windows. Each session executing the batch script just need to update the table name in the script from [bloat_1] to [bloat_2], [bloat_3] and so on to avoid creating the same table in the same database prior to executing the script.
USE [RampUpBufferPool]
GO
drop table if exists [bloat_1];
create table [bloat_1] (Idx int, large_col char(7000) default 'a', PRIMARY KEY (Idx));
set nocount on;
declare @i int = 1;
while @i <= 10000
begin
insert into [bloat_1] (Idx) values (@i);
set @i += 1;
if @i = 10000
begin
set @i = 1;
truncate table [bloat_1];
end
end
- At point-in-time 3, SQL Server quickly ramps up its buffer pool allocation as soon as the batch script starts execution.
- Over time, the Total Memory (KB) will reach the same value as the Target Memory (KB).
- SQL Server will not be able to allocate buffer pool over the configured maximum memory, hence the Total Memory (KB) value will not go beyond the Target Memory (KB) value.
- Setting SQL Server maximum server memory is a good practice to ensure there is always enough memory available to the Operating System at all times.
Point-in-time 4: Increasing SQL Server Maximum Memory
Increasing SQL Server maximum memory to 4 GB.
EXEC sys.sp_configure N'max server memory (MB)', N'4096'
GO
RECONFIGURE WITH OVERRIDE;
GO
- At point-in-time 4, when SQL Server maximum memory is set to 4 GB, the Target Memory (KB) counter reflects the 4 GB value in the next second.
- As SQL Server has its maximum memory increased by 1 GB, the Total Memory (KB) counter is observed to ascend quickly to consume the additional 1 GB buffer pool allocated to it.
- SQL Server continually ramps up its buffer pool allocation to a total 4 GB as indicated by the Total Memory (KB) in the blue line.
- Once the Total Memory (KB) counter reaches 4 GB, it does not go beyond the configured SQL Server maximum memory setting which is represented by the Target Memory (KB) counter.
Point-in-time 5: Lowering SQL Server Maximum Memory
We will execute the script below to lower the SQL Server maximum memory to 2 GB. This is not a good practice and is not an action that you would perform especially in your production SQL Server.
EXEC sys.sp_configure N'max server memory (MB)', N'2048'
GO
RECONFIGURE WITH OVERRIDE;
GO
- At point-in-time 5, Target Memory (KB) value drops to the 2 GB mark in the next second to reflect the configured 2 GB maximum server memory. In this scenario where the Total Memory (KB) > Target Memory (KB) is an indication that SQL Server memory manager signal will adjust its buffer pool size.
- Approximately four seconds later, SQL Server memory manager starts deallocating memory from its buffer pool. The process took 9 seconds to flush out 2 GB worth of data pages from its buffer pool.
- In all the five scenarios, we can see Target Memory (KB) represents the configuration setting of the SQL Server maximum memory setting.
- The Total Memory (KB) counter which represent the current size of buffer pool will try to align its value to the Target Memory (KB) value which in our scenario is the SQL Server configured maximum memory.
- For a SQL Server that has been running for a while with a workload, we will see the Target Memory (KB) = Total Memory (KB). This is not surprising given the SQL Server configured maximum memory is the buffer pool size that it can use.
Summary
This tip gives an insight on performance counters Target Memory (KB) and Total Memory (KB) behavior and its correlation with SQL Server maximum memory setting under normal circumstances.
When SQL Server maximum memory is not set, the memory manager sets a reasonable Target Memory. After restart of the SQL Server service, SQL Server memory manager does not aggressively ramp up its memory usage to the setting of SQL Server minimum memory setting if there is not a workload. But when SQL Server has reached its configured minimum memory, SQL Server does not shrink its buffer pool lower than this configured value.
As for SQL Server configured maximum memory, SQL Server does not grow its buffer pool higher than this configured value.
Next Steps
