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



  • 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?



  • 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 https://www.erikdarlingdata.com/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 https://www.mssqltips.com/sqlservertip/6571/perfmon-counters-for-sql-server-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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2