I have SQL Server 2016 with 2 instances., on i5-6400, 16GB ram, and 1TB raid 1 with 2 disks.
First instance, large databases, many users, and Second instance much smaller databases, much less users.
After server restart, everything is normal some time, I mean access database speed and no lag at all.
But after some time, First instance takes almost all, about 90% of available RAM, and then
access to Second istance is poor, much slow then usualy, and lag is present.
Is there any preferances that should be set on SQL server in order to First instance take less RAM ?
Thanks in advance.
In SSMS object explorer, right-click the server name, properties, memory and set Maximum Server Memory. The number you set there is in Mega Bytes.
Remember to leave some memory for the operating system after the memory taken up by the two instances - about 2 GB.
And get more RAM! RAM is cheap and will greatly help performance overall.
I just noticed this - is this system very important...what would happen if you lost the system and it was no longer available?
The reason I ask is that you have stated you have a 1TB raid 1...and that is all you have for storage. That is extremely risky and if anything happens to that storage you lose everything.
I make, on daily bases, SQL database backups, and copy them on other computer. And once weekly I copy backup to USB flash drive, and copy them to home storage.
I think that is safe enough.