Okay, what am I doing wrong here - I have set the Max Server Memory setting to 16GB for this instance. Memory shows as 100% in use, but the numbers below don't add up. This is running a 100GB database. Even if I run SELECT * queries from huge tables, I can't get the buffer cache counter to go up. What is going on? Why does it say 100% in use?
Anyone have any ideas? I've had about enough of this product and will probably move to another solution that is clearer and updated more frequently.
The buffer cache is only one part of the total SQL Server memory. If you click on either the Buffer Cache or Memory used bars, you will get a drill down prompt. You can click on the Investigate hyperlink to get more details. The memory used drill down will show the space used by different segments in the server memory. But possibly more helpful for you, the buffer cache drill down will show which object are using how much of the buffer cache. This will include the table from your SELECT *. These views should help you understand how the different memory segments of the total SQL server memory are being used.
I hope this helps,
That metric is telling you that the memory on the host server is being used at 100%. It appears that SQL Server is using a little over 10.5 GB of the 16 that is available. My guess is that there are other OS processes that are consuming the other 5.5GB of memory, or that perhaps if that machine is a VM, the balloon driver has stolen some free memory to give back to the host.
The memory summary drilldown may provide a bit more insight to see if any of the other SQL Server memory spaces are consuming additional space, otherwise you will want to look into the OS processes to find out where that additional 5.5GB of memory is allocated. If OS processes are consuming memory, and no free memory is available, SQL Servers buffer cache may not grow beyond what you are seeing as there is no where to take memory from.
Lastly, these numbers come straight from SQL Server. If you can show some discrepancy in what Foglight is telling you versus what SQL Server shows in it's own native counters/dmv's, please report back so that we can determine if there is an issue with our data collection.
Memory Summary drilldown for reference.
I believe the Memory Used metric is an indicator of how much memory of overall system memory is being used by SQL Server as a percentage. In other, words all of the RAM on this machine is being used by SQL Server (100%).
Here's what I get when I drill down. 6GB of "Free Pages". Where does this information come from? If I run a SELECT * on tables way bigger, I would expect that buffer cache to increase, but it doesn't. I have the Max Server Memory set to 16GB on this machine. (Total of 24GB installed). Why does the earlier screen say 100% of RAM is in use?