We have multiple servers running multiple instances where I work. The assumption is, that using one piece of hardware for multiple instances, will work pretty good for smaller databases that are used once or twice a day.
This also allows us to run individual instances with a dedicated IP address for certain departments/councils/customers.
The big advantage is the licensing as Microsoft SQL Server permits you to install multiple SQL Server instances using the same one license. This depends on the Edition (Standard / Enterprise) and the CAL or licensing agreement you have signed with your reseller.
Prior to SQL Server 2000 (8.x), only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 (8.x) introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 (9.x) introduced the SQL Server Browser service as a replacement for SSRP.
Reference: SQL Server Browser Service (Microsoft | SQL Docs)
However, the SQL Server instances are all running on one Windows Server and the Windows Server distributes the "consolidated" resources of the underlying "hardware" (physical or virtual), be they:
- Disk I/O or SAN I/O
You can configure the instances to run on certain CPUs with the Resource Governor.
You can configure the SQL Server instances to use only a certain amount of RAM and if there is enough RAM, then each instance will normally use different memory spaces. However, in a virtual environment e.g. VMware the host system can decide to assign the same RAM as other executables.
What doesn't work
max degree of parallelism (MAX_DOP) to a value of 3 will have no effect, as this is the setting for parallel execution of e.g. index scanning/seeking. A SQL Server instance will run on all available CPU cores, regardless of the MAX_DOP setting.
cost threshold for parallelism (CTFP) settings will only have an effect on the level at which the Query Optimizer might decide to use parallel processing for queries.
Based on my current experience with +1000 databases running on +400 instances on +100 servers, I would say that there are cases where it can work.
But in other cases with large databases and a large amount of users querying lots of data the whole day, you can reach a limitation in the available performance for a given instance, which will force you to install a single instance for a single database. This would be the opposite of what you are trying to achieve.
You will have to build up knowledge of your databases, the way they are queried and be able to determine when a database is performing well or not. Visiting Performance Tuning workshops can assist you in achieving these goals.
That's the best advice I can give you to your general question.
Additional Considerations Based on Comment
Well I wouldn't put two "small" database in separate instances, unless you require some sort of real separation, because users or programs are using
sa to login to the database or because you require some form of network separation.
Security / Legal / Audit
There may be legal / audit requirements where you have to separate the instances, just to ensure that one person can't "accidently" access a different database.