Two server instances on same host, what benefit? No dedicated CPU

Question

I'm doing some checks on a client's host. I found that they have installed 2 instances on the same host. The VM has 16 logical processors but are not associated with either instance, meaning they are managed by the service. So both installations could use them. I saw that they correctly divided the memory into two equal parts, leaving the correct remainder to the OS. I wonder what benefit to doing this type of installation? I would have made two different hosts, perhaps dividing the processors, given that the costs would have been the same... they are virtualized environments anyway.

I asked them why and they told me that the environment was created by others and that they, not being experts, are preserving it. I checked the CPU has an average usage of around 22% with peaks rarely above 27%. I would be inclined to merge everything in a single instance also to simplify the infrastructure. They have about 7000 databases that they split between the two instances... but they are small of size and low in usage... only in the morning.

Currently I have not found any vital performance counters totally wrong, mostly incorrect DB settings given that they come from SQL SERVER 2008 with a compatibility mode 100 (on 2019).

What do you think?
Thank you Alen

Answer 1

I think your idea of consolidating the two instances into one single is sound.

There can of course be valid reasons to have multiple instances. One reason is that you to isolate logins from different customers/organisations from each other. But that sounds far-fetched with two instances and 3500 databases on each.

Another possibility is that there are different server collations or some other configuration, and this is something you should investigate before you go ahead. (But that configuration difference may be accidental, so you may go ahead anyway.)

There could also be a different set of jobs, but having jobs for this many databases sounds like a nightmare.

From a performance perspective, it may not matter that you are consolidating the instances, but it's unlikely to be detrimental. And management should be easier.

And, yeah, moving to compat level 150 is appealing. You could start with a few selected databases.