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.
- 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.
