This is for a sanity check to make sure I am correct in my thinking -
Current Set up - Server farm
80GB memory on server
2 discs - C:OS
E:Data - This is many drives with guaranteed specific performance threshold (I may not be saying that correctly) The discs on the SAN are setup to not be a potential latency bottleneck
CPU - 6
The team has set up 15 instances on this server of SQL 2016/2017 Always on. Thy have the minimum RAM set to 3GB. The max ram on the instance is not set. I am not really sure why instances were used vs one instance. I believe it was done for security. Within an instance there can be multiple DBs and those DBs can have potentially hundreds of users. I do not have a metric on users per database.
What we are seeing -
We are seeing in RAM consumption is trickling down RAM to 3 gb minimum for end of the line instances. The first instance could be running with 40GB. I am suggesting that Min/Max should be set per instance and reserving RAM for the OS. That each instance based on User/database size dictates the percentage of RAM associated to the instance. Does that seem correct or does dynamic memory management take care of all this manual manipulation? For a setup like this I was trying to suggest that each instance really needs a baseline of RAM we go off of 10GB-30GB depending on users. The OS should have its own 64GB just to maintain itself.
I would like to monitor each instance CPU usage since all instances are using the same 6 CPU's. Beyond System Health, memory grant and data size, check CPUUsage xevents do you know of anything else I can prove that we need to either move away from multiple instances or dedicate CPUs to an instance so they aren't stealing from each other. If you have DMVs I can use please share.
Potentially you guys give me some patterns to look at. This seems to be a classic server farm setup that is not really the best setup in later SQL servers. As this process continues the team will load more instances on the server as we get more customers. If you have case studies or better patterns I would like to read about them.
Thanks in advance