Baseline SQL performance on server with stacked instances

Question

Baseline SQL performance on server with stacked instances

Cooper, David A. [ISS] (Contractor) 111

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.

0 comments

2 answers

Your answer

Answer 1

There are certainly more than one way to skin this cat. You could:

Put all databases on the same instance.
Have one instance for each application (i.e. one application could have multiple databases).
Have one virtual machine for each application.

You have chosen the second option. One advantage is isolation, there is no risk that users of one applications will see the databases of other applications, or even the users of the other applications.

One disadvantage is less flexibility. You will need to have one AG per application, which I believe will take more cluster resources. With all databases in the same instance, you can group several applications into the same AG - just put them all in the same AG!

You say:

each instance has it's own TempDB, tran logs, index maintenance jobs

True, this way there will no competition on tempdb, but it also means less flexibility. Instead of all applications sharing a big tempdb, each application will have its small tempdb, and one application may fill up its tempdb, while others have plenty of space in theirs. When it comes to transaction logs there is no such concern, since they are per database, so from that aspect one or many instances does not matter that much. The same applies to index-maintenance jobs, since they can be configured per database, so if you want different maintenance policies you can achieve that on a single instance. With many instances, you will have to administer more.

An important thing is how you handle CPU and memory. You talk about dividing the available memory to each instance. The same thing as with tempdb applies here, but since SQL Server clings to the memory it has, this strategy probably works better than for tempdb. Just monitor how much memory the applications use, so you don't have unused memory on one instance that another instance could benefit from. When it comes to CPU, you consider setting CPU affinity so that the instances do not compete with each other. But of course, this means that some cores will smoke at the same time as other instances are idle. But if you don't affinitise, bad performance in one instance, and impact the other instances. True, if you put all in one instance, this will also happen, but it will be easier to understand when you can see the entire activity in that instance.

But in the end what matters is the business requirements. If business owners do not accept a shared instance, you will have no choice.

How do I know when the server has reached a saturation point when adding multiple instances/workloads?

To start with, how many instances do you expect? I seem to recall that Microsoft supports up to 50 instances, but I think you should stop well before that number. Even ten instances sound many to me. Maybe smaller applications should share an instance, and only bigger or more secret applications should have their own instance.

If you split up memory and affinitise the CPU, you will know when you have exceeded the limits that way. Same goes for the space of the tempdbs. You could "overprovision" the CPUs, but I am less keen on overprovision the memory and tempdb space.

All and all, the question is not too different from when you migrate to a single instance, but it's more complex to collect the data, and you would need to rely more on what Windows offer.

Answer 2

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

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