Why is total and target server memory never reaching the configured memory value?

Question

I have sql server with 100GB ram, of which 90GB is allocated to sql server. 2 GB is currently free as shown in task manager.

90GB=92160MB (as seen in the sql server memory settings)

I am monitoring the total server memory and target server memory perf monitor counters.

I can see that the target server memory never crosses 90150MB, and the total server memory never crosses 89100MB. What could be the reason for this?

My understanding was that target memory should always be equal to the setting configured in sql server memory settings. But why is this different in my situation?

Answer 1

My question was that - I have set target to 92160MB, and its a very busy database server, so why is total and target values not reaching 92160MB?

Learning_DBAdmin's answer basically covers this question. The short of it is it doesn't matter how busy your server is per se, if you're not consuming enough data pages to fill 90 GB of Memory, then your SQL Server instance isn't going to take 90 GB of Memory from the server.

The busiest server in the world could only operate on 10 GB of data and then the other 80 GB of Memory would go to waste, roughly speaking. There are other things SQL Server uses Memory for such as caching execution plans, but a major consumer of Memory is usually the data pages for the data you're querying, and if it's not a lot of data at a given time, then not a lot of Memory is needed.

For perspective, I used to manage a multi-terabyte database, the biggest table being roughly 1.5 terabytes alone with 10s of billions of rows in it. The server that SQL instance ran on only had 16 GB of Memory and that was sufficient for the types of queries that were ran on it. It also was very heavily transactional (very write busy), writing thousands of rows every minute. This is because at any given time, the amount of data loaded off disk and into Memory was never more than a few GBs, for the queries ran on it. It didn't matter that on disk the data was multiple terabytes, nor that the server was very busy writing new data every minute.

To your follow up question "My understanding was that target memory should always be equal to the setting configured in sql server memory settings. But why is this different in my situation?"

I don't normally use PerfMon anymore for measuring pressure, and as I've advised before, Erik Darling's sp_PressureDetector is probably a better tool to get a wholistic view of Memory pressure. That being said, Target Memory "is the amount of memory that SQL Server can potentially allocate to the buffer pool" as discussed in SQL Server Perfmon Counters - Target vs Total Memory and Max Memory. What that means (as I previously stated) is that this is the amount of Memory that can be used to cache data pages from disk. But it doesn't mean that SQL Server will automatically use that much Memory, especially if you aren't querying enough data to fill the buffer pool.

Answer 2

"My understanding was that target memory should always be equal to the setting configured in sql server memory settings."

Nope. Consider that the default value for [Max Server Memory] when installing SQL Server is 2,147,483,647 megabytes. That's one mb shy of 2 PB :-)

Let's assume the SQL Server instance in question does not have startup trace flag 834 enabled along with LPIM. Those instances play by their own rules and unless that's the case here, no need to discuss it. It's a pretty rare configuration.

[Max Server Memory] is the maximum value for [Target Server Memory]. But, if [Max Server Memory] cannot be achieved based on the total amount of RAM, [Available memory] and [Free & Zero Page List memory], SQL Server will select a lower value for [Target Server Memory]. [Target Server Memory] typically governs [Total Server Memory], and [Total Server Memory] is the sum of [Database Cache Memory], [Free Memory], and [Stolen Server Memory].

Below is a graph of a system that shows a wobbly [Target Server Memory]. As activity progressed on this system, memory use other than SQLOS [Target Server Memory] (note - this could even be the sum total of 2 mb private memory per SQL Server worker since that memory is not included in [Total Server Memory]) occasionally causes a low memory notification from the OS. SQL Server is a considerate neighbor and if allowed by the [Min Memory Setting] will lower [Target Server Memory] by an amount that should resolve the low memory condition. (as an aside this is why i usually recommend keeping [Min Server Memory] at the default setting of 0 - unregulated)

In your case, [Target Server Memory] is about 2 gb lower than [Max Server Memory]. Unless both LPIM and trace flag 834 are enabled for this instance, that would be due to memory commitments other than SQL Server lowering [Available] and/or [Free & Zero Page List] memory. SQL Server knows the [Max Server Memory] value cannot be achieved or maintained without causing low memory notifications (or perhaps has already received notifications), so a lower value has been selected for [Target Server Memory].

Montoring [Available] and [Free & Zero Page List] memory should confirm this state of affairs. Very likely, as on the system these graphs came from, paging space is also being used on the system.

Perhaps there is another significant memory consumer on the system. SSIS, SSRS... or in the case of a VMware VM, the balloon driver. If another memory consumer can be identified and eliminated or restrained, a higher [Target Server Memory] value should be seen - and it may even reach [Max Server Memory].

However, even if another memory consumer is identified and addressed, for [Target Server Memory] to reach and stay at [Max Server Memory] may also require lowering the [Max Server Memory] value.

I personally always prefer a very steady [Target Server Memory] value equal to [Max Server Memory] to a wobbly [Target Server Memory]. For the sake of resource utilization and performance evaluation, i also prefer that to a steady [Target Server Memory] value which remains a fixed amount below [Max Server Memory].

Answer 3

Target Server Memory is how much memory the engine is willing to use. You can track this with the Perfmon counter SQLServer:Memory Manager – Target Server Memory (KB):

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Target Server%';

Generally, when you start up SQL Server, the target is set at your max. However, SQL Server doesn’t allocate all of that memory by default. (Again, the experts wanna raise hands and prove how much they know – zip it.)

Total Server Memory is roughly how much the engine is actually using. (Neckbeards – seriously – zip it until the end of the post. I’m not playing.) After startup, SQL Server will gradually use more and more memory as your queries require it. Scan a big index? SQL Server will start caching as much of that index as it can in memory. You’ll see this number increase over time until – generally speaking – it matches target server memory.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Total Server%';

Since your target server memory and total server memory is almost same, it is not changing and not increasing.

In most cases, it’s not quite that black-and-white, but you can still use the speed at which Total Server Memory rises after a reboot to get a rough indication of how badly (and quickly) SQL Server needs that memory. If it goes up to Target Server Memory within a couple of hours, yep, you want that memory bad. But if it takes days? Maybe memory isn’t this server’s biggest challenge.

All of the above is from Brent Ozar's nice article about memory pressure and understanding them through performance counters.

As mentioned in my answer to your other question, you may need to focus more on the actual root cause through wait statistics, it doesn’t seem to have memory pressure from the provided values to me.

Hope this helps.