January 11, 2022 at 10:20 pm
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
January 12, 2022 at 10:16 am
I would suggest setting max ram per instance.
As far I know SQL Server keeps consuming memory and never releases it, potentionally not leaving enough ram for OS, AV, ...
It can be better now, but there was a time sql server never got to increase the requested memory because as far as sql server was concerned the vm had already reached it limit.
January 12, 2022 at 3:06 pm
I don't have documentation on this kind of setup, sorry. I've never run more than 2-3 instances on a server.
However, heck yes, you need to have max memory set.
@jo-2 Pattyn said it and I agree 100%. SQL Server will absolutely consume all the memory it can, and won't give it back unless forced. So, not surprising with 15 instances you have a ton of memory contention. Here's a good article on it that goes into a lot of detail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2022 at 4:22 pm
You must use the max memory setting when you put more than one instance of SQL on a given box, for the reasons others have stated above.
And, for that many instances, get more RAM! RAM is a one-time cost, making it the most relatively inexpensive way to boost SQL performance.
More CPUs would help too -- especially if you schedule jobs on those instances -- but that increases the licensing costs, so they may not want to do that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 12, 2022 at 5:31 pm
Thanks for the replies on the sanity check. That is what I assumed. Also I think we have way to many instances on a cluster. I will need to document and find a best practice somewhere to change this pattern. I will investigate how it got this way and why.
January 17, 2022 at 10:02 am
It is not clear from your original post: Do you have 1 server of 80 GB with 15 SQL instances on it, or do you have 15 servers of 80 GB each with one instance of SQL on it. Also are you running the server instance on physical kit or is it running as a guest. If it is a guest what is the capacity of the host.
The advice above about setting max memory for SQL is important. But you say you allow 64 GB for OS memory, this is far too much.
Can you give more details of your setup. If possible upload a diagram showing hosts, guests, SQL instances, AO topology, etc. If you can find the reason for 15 instances this could be helpful, you are using a lot of resources (resource=money) simply keeping 15 running even if they do no work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply