1

We have TWO of: your standard 2-node active/passive Windows Server 2008 R2 with SQL Server 2008 SP1 Cluster with 1 TB of RAM on the active node and 512 GB on the passive (same on both clusters).

Both OS and SQL "see" the 1 TB of memory and additionally set the following: "min memory" setting to 450 000 (MB) and "max memory" setting to 950 000 (MB)

The problem: every possible monitoring (perfmon metric; red gate sql monitor, task manager, resource monitor etc....) and every possible DMV query show that BOTH clusters cap at exactly 432.6 memory used (PerfMon reports the following for "SQLServer:Memory Manager - Total Server Memory " = 432.6 GB)

I understand the concept behind "it will only use as much as it needs", but I'm certain it can stuff more in there, because the clusters each host 2000+ databases for a total of more than 2 TB worth of often accessed data.

PLUS why would BOTH cluster need exactly 432.6 GB?

Improve this question

1 Answer 1

Reset to default
4

Sounds right to me 432.6 GB = 443,000 MB

The SQL Servers are consuming less than "min memory" because that is all they need.

SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

That is, your working set of data does not exceed min memory and this is false:

total of more than 2 TB worth of often accessed data.

Improve this answer
6
  • hmmm, but both clusters capping at 432.6? any reason they have an identical need? They do each host 2000+ dbs, but those are different. also, according to this answer that means I could start lowering the "min memory" and see what happens? If this is the case, if I lower "min memory" to -say- 200 000 MB - I should expect no change in the metric "SQLServer:Memory Manager - Total Server Memory " and it should remain 432.6 GB ??? Thx,
    – Tome Popcevaliev
    Commented Jul 26, 2017 at 10:36
  • It is not capping. It is simply not using any more
    – gbn
    Commented Jul 26, 2017 at 10:54
  • Lowering min memory to say, 250 will probably show around 250 used and then it will go up if required by the load
    – gbn
    Commented Jul 26, 2017 at 10:55
  • thank you @gbn I have lowered it to 256 and I will report back here with what has happened.
    – Tome Popcevaliev
    Commented Jul 26, 2017 at 11:49
  • hi, so to follow up - after I lowered "min memory" there has been NO change whatsoever. Should I conclude that that's it. Both clusters need exactly 432.6 of memory and not byte more? And they've been needing that same amount for months, even though the total size of all DBs hosted on them has grown at least 10%? I'm not sure all these facts are adding up ... Is there ANYTHING else that might be the case? ANYTHING? Thanks
    – Tome Popcevaliev
    Commented Jul 27, 2017 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.