Baseline SQL performance on server with stacked instances

Question

My question is what approach to take to baseline performance with multiple SQL Server instances on a server. We are building a Always On availability group cluster (SQL 2022) for a consolidation project. The cluster is as yet unused. We plan to stack multiple instances. There are a few reasons for stacking SQL instances. For example, a business customer may request to dedicate an instance to their app. Also, we think it is better to separate workload on multiple instances where each instance has it's own TempDB, tran logs, index maintenance jobs, etc. That way, it is easier to isolate and t-shoot any performance issue, set different config options per instance, etc. We do know to divide total system memory (1 TB) to each stacked instance. We also realize that the memory allocated to an instance will go unused until we actually migrate a workload to that instance. My approach would be to migrate app #1 to instance #1, benchmark that; migrate app #2 to instance #2, benchmark that; etc. I would capture both instance-level metrics as well as server/OS-wide metrics like CPU utilization. Does this sound like an appropriate model? Do you have any more guidance? How do I know when a given instance or the server as a whole has reached a workload saturation point when adding multiple instances/workloads? Thanks.

Answer 1

Your phased approach is appropriate: establish an idle host baseline, migrate one representative workload at a time, measure it alone and then in combination, and stop adding workloads when shared-resource pressure or application/AG service-level objectives show sustained degradation.

Recommended baseline sequence

1. Record the empty-platform baseline
   • Capture the configuration of every node and instance.
   • Measure idle CPU, available memory, storage latency/throughput, network traffic, and AG synchronization overhead.
   • Test backup, restore, and planned failover while the system is otherwise idle.
2. Baseline each application before migration Capture representative peak periods, not averages alone:
   • Normal business peak
   • Batch, ETL, and maintenance windows
   • End-of-day/month/quarter processing
   • Backup and integrity-check windows
   • Application response time and throughput
   A useful baseline includes peak/off-peak periods, query or batch response times, and backup/restore completion times.^1^
3. Add workloads incrementally
   • Run application 1 alone and record its baseline.
   • Add application 2 and repeat the same tests.
   • Compare application 1 before and after application 2 was introduced.
   • Continue with each workload, including intentionally overlapping maintenance jobs.
   • Run the same test on every possible AG primary node. After failover, one node may host a different combination of primaries than under normal operation.
4. Keep two levels of measurement
   • Host level: determines whether the shared hardware is saturated.
   • Instance/database level: identifies which workload is creating pressure.

PerfMon is suitable because it can correlate Windows and SQL Server counters in the same capture.^2^

Minimum measurements

Host-wide

• CPU utilization per logical processor and total
• Processor queue length
• Available memory and paging
• Storage read/write latency, IOPS, throughput, and queueing for each volume
• Network throughput and latency
• SQL Server process CPU and working set for each instance

Per SQL instance/database

• Query Store runtime and wait history
• Wait-statistics deltas over matching test intervals
• Batch Requests/sec and Transactions/sec
• Compilations and recompilations/sec
• Blocking and deadlocks
• Total Server Memory, Target Server Memory, page reads/writes, and page life expectancy
• Log bytes flushed, log flush latency, file I/O latency, and file growth events
• tempdb usage, allocation waits, and spill activity
• Backup and maintenance duration

On NUMA servers, evaluate page life expectancy per Buffer Node, rather than relying only on the host-wide average.^3^

Memory configuration

Do not divide 1 TB equally unless the workloads genuinely have equal requirements. Reserve memory for Windows, allocations outside max server memory, AG and backup activity, monitoring agents, and every other instance; allocate the remainder proportionally to measured demand.

Microsoft supports three multi-instance models: per-instance maximums, minimums, or both. The sum of maximum allowances should not exceed available physical memory, and leaving all instances unlimited is not recommended. Also, max server memory does not constrain every allocation made by the SQL Server process.^4^

Check each instance's current bounds with:

SELECT
    name,
    value AS configured_value_mb,
    value_in_use AS active_value_mb
FROM sys.configurations
WHERE name IN
    ('min server memory (MB)', 'max server memory (MB)');

Keep min and max separated rather than setting them equal or nearly equal. The settings can be changed without restarting, which allows measured adjustment after each migration.^4^

Defining saturation

There is no reliable universal “maximum number of instances.” Define saturation as the point where adding load causes one or more of these conditions during a representative sustained interval:

• Application response time, throughput, or batch duration breaches its SLA.
• CPU remains consistently around 80–90%, particularly with a growing processor queue.^5^
• Available memory becomes persistently low, instances remain at their memory targets, and physical reads or memory-related waits rise.
• Storage latency increases while measured throughput approaches the documented capacity of the complete I/O path. Validate throughput using Disk Bytes/sec, Disk Read Bytes/sec, and Disk Write Bytes/sec; storage capacity includes the whole path, not only the disks.^6^
• Existing workloads deteriorate materially after another workload is introduced, even if no single host counter is at 100%.
• Log send or redo queues grow continuously instead of draining after bursts, increasing RPO or failover time.

For the AG, capture log_send_queue_size, log send rate, redo_queue_size, redo rate, bytes sent/received, and flow-control time. These distinguish primary capture, network transport, secondary log hardening, and redo bottlenecks.^7^

Isolation limits

Separate instances provide configuration, security, tempdb, job, and memory boundaries, but they still compete for host CPU, storage paths, network, and OS resources. If CPU contention eventually requires hard partitioning, processor affinity is available, but Microsoft describes it as specialized and states that default Windows affinity normally performs best.^8^

Also stagger backups, index maintenance, integrity checks, and statistics jobs unless concurrent execution is part of the capacity test. Test both the normal placement and the worst credible post-failover placement, and retain headroom for node loss rather than sizing only for steady-state utilization.

---

References

1. Establish a Performance Baseline
2. Monitor Resource Usage (Performance Monitor)
3. Monitor memory usage
4. Server memory configuration options
5. Monitor CPU Usage
6. Troubleshoot slow SQL Server performance caused by I/O issues
7. Monitor performance for Always On availability groups
8. Server configuration: affinity mask