It’s a very open ended questions and before answering this question you need to understand what kind of application you are using and how much transaction they makes on database. You need to understand size of databases and how much CPU\MEM is required to run those databases on SQL server also it more depends on your performance capacity of Physical Server\VM, I mean if you are having a server with multiple cores 100 Gigs of mem and super-fast SAN you can host multiple instances.

This kind of consolidation of instance has its own Pros and Cons as this could be a beneficial but it can also be a headache for a DBA or Architecture team.

Why Would You Use Multiple SQL Server Instances?

• You can save lot of cost for hardware
• License costs for SQL Server currently are by CPU socket by machine, not by instance on a machine. Doubling up the instances on a machine can save lot of cost
• Fully utilize the hardware performance to its limit

Performance Bench-marking is very important for consolidation (running multiple instances on server), this bench-marking will provide you information like how much MIN\MAX CPU and how much MIN\MAX memory is required to run this SQL Instance, also how much storage capacity is required. Apart from these things network bandwidth and I/O plays an important role here before making decision to host multiple instances on one Server.

What Important counters I must collect for defining my benchmark of SQL Instance?

Identify the Physical configuration of your server on which you are hosting this SQL Instance

You need to focus on various trends for below performance counters to define minimum, average and maximum value required for your SQL Instance to run.

1. Identify CPU utilization trend over a period of time to identify CPU required to run SQL Instance: This will let you decide how much computing power\processors are required to host this SQL instance.

2. Identify CPU utilization of database: You can easily get this information from T-SQL what is the average utilization of CPU for your database, a job can be configured on a server and you can draw a trend and actually see which database is consuming most CPU

 WITH db_cpu_stats 
 AS (SELECT databaseid, 
 db_name(databaseid) AS [database name], 
 sum(total_worker_time) AS [cpu_time_ms] 
 FROM sys.dm_exec_query_stats AS qs 
 CROSS apply (SELECT CONVERT(int, value) AS [databaseid] 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle) 
 WHERE attribute = N'dbid') AS f_db 
 GROUP BY databaseid) 
 
 SELECT 
 
 GETDATE() AS CAPTURE_DATE, 
 [database name] AS 'Name', 
 [cpu_time_ms] AS 'CPU Time [MS]', 
 cast([cpu_time_ms] * 1.0 / sum([cpu_time_ms]) 
 OVER() * 100.0 AS decimal(5, 2)) AS 'CPU [%]' 
 
 FROM db_cpu_stats 
 WHERE databaseid <> 32767 -- ResourceDB 
 ORDER BY cast([cpu_time_ms] * 1.0 / sum([cpu_time_ms]) 
 OVER() * 100.0 AS decimal(5, 2)) desc 
 OPTION (recompile)

 

3. Identify Memory utilization of database

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';
;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

4. Identify below counters usage to define MIN\MAX memory and other memory configurations.

These counters will help to understand about the load on the SQL Server. Monitor these counters over a period of time and analyze the trend that will actually give you min max and average utilization of these parameters

5. Identify the database usage trends to define capacity management

Apart from all this you need to focus on the of I/O sub system, Your I/O subsystem must be fast and further separation your Logs, Data and TEMPDB data files, will give you an additional advantage

Once you define you bench-marking on the basis of above counters which will help you in defining cpu, memory, storage and bandwidth requirement for a SQL Instance.