Separate instance names don't give each workload a separate machine. With several SQL Server instances on one host, memory and CPU remain shared. Budget the combined load before deciding that every instance can keep its old settings.
Inventory Several SQL Server Instances by Service
Each instance runs its own engine service and manages its own databases, configuration, and tempdb. The Windows host supplies the shared resources. A default instance and named instances can coexist.
Different connection names don't make their resource demands independent. Record service state, version, edition, owner, and workload for every instance before calculating the combined capacity budget.
I start with installed engine services and then connect to each approved endpoint for its identity. A stopped service is still an installed instance that can return later. Don't build the inventory only from active network listeners.
The PowerShell command below reads local Windows service metadata. It doesn't discover remote machines or prove that every application knows its correct endpoint. Keep client connection inventory as a separate task.
# PowerShell
Get-Service | Where-Object {
$_.Name -eq 'MSSQLSERVER' -or $_.Name -like 'MSSQL$*'
} | Select-Object Name,DisplayName,StatusAdd Memory Budgets for Several SQL Server Instances
Max server memory is configured independently on each instance. Its scope doesn't cover every allocation in the entire SQL Server process. Leave capacity for Windows, other services, and engine allocations outside the main governed pool.
Add the intended ceilings across instances, then compare them with physical memory and expected concurrency. Several independent generous defaults can create a very ungenerous shared operating system.
I test simultaneous peaks instead of combining average memory charts. A nightly report and a load job can overlap even when their daily averages look small. Include maintenance, backups, and failover behavior in the budget.
Record each instance's approved limit and expected workload. The query below provides identity and settings from the instance you connected to. Repeat it separately on every engine service in the inventory.
SELECT @@SERVERNAME AS InstanceName,SERVERPROPERTY('InstanceName') AS NamedInstance,
SERVERPROPERTY('ProductVersion') AS ProductVersion;
SELECT name,value_in_use
FROM sys.configurations
WHERE name IN (N'max server memory (MB)',N'min server memory (MB)',
N'max degree of parallelism',N'cost threshold for parallelism');Apply One Approved Limit at a Time
Use a reviewed per-instance memory value, not a fraction chosen automatically from the instance count. Workloads need different budgets. The example below uses a placeholder ceiling for an isolated instance configuration exercise. It changes an instance-wide setting, so run it only on the intended instance in an approved window.
Replace it with the approved value and preserve the original setting first. Review the effective value and workload afterward rather than assuming the configuration command has already established healthy host memory.
A ceiling isn't a reservation guaranteeing memory to that instance. Raising minimum memory isn't a simple way to create reliable isolation either. With several SQL Server instances, resource pressure still occurs at the host boundary.
Coordinate the whole plan so one instance's tuning doesn't consume another's headroom. A local improvement can create a host-wide regression. The person changing the setting needs the shared budget in view.
EXEC sys.sp_configure N'show advanced options',1;
RECONFIGURE;
EXEC sys.sp_configure N'max server memory (MB)',8192;
RECONFIGURE;
Keep CPU Affinity and MAXDOP Separate
Processor affinity controls which CPUs an instance can use. MAXDOP limits parallel worker use for an individual request under its applicable rules. It doesn't cap the instance's total CPU usage.
Many concurrent serial queries can still saturate a host. Review NUMA placement, available schedulers, and concurrent workload before choosing either setting. A number copied from a standalone server doesn't automatically fit shared hosting.
Automatic affinity is a sound starting point unless measured evidence supports deliberate partitioning. If assigning CPU sets, coordinate every instance and Windows overhead. Don't give each instance the same supposedly exclusive set.
Changes can affect scheduler and memory locality behavior. Use a supported configuration method and a reversal plan. CPU partitioning should solve identified contention, rather than make an infrastructure diagram appear more orderly.
SELECT scheduler_id,cpu_id,status
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY scheduler_id;Assign Endpoints Without Ambiguity
Each TCP listener needs an available port. Named instances can use dynamic ports and discovery through SQL Server Browser, or a planned static endpoint. Inventory the actual application path before changing it.
SERVER\REPORTING is an instance name, while a hostname with an explicit port supplies another connection route. Keep monitoring and client configuration aligned with the chosen approach instead of relying on remembered defaults.
Check the active connection's port through the DMV. A non-TCP local connection can return NULL, so validate through the intended TCP route. Review approved firewall rules and the listening configuration with the Windows owner.
A port belongs to the endpoint contract. An uncoordinated port change can make a healthy instance appear unavailable. Another instance on the host can still answer normally.
SELECT @@SERVERNAME AS InstanceName,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;Monitor Host Pressure Across Several SQL Server Instances
Collect process memory and host memory state from each instance. Host counters repeat across the same machine, while process counters describe the connected engine process. Don't sum the repeated host total to create imaginary capacity.
Keep the instance identity beside each result. Also monitor storage, tempdb, blocking, and concurrent CPU activity with the approved host and SQL monitoring paths. Memory alone doesn't establish whether consolidation is working.
Which two workloads peak together during maintenance? Rehearse that overlap, including backups and restores. Save a baseline before changing budgets and compare after each step.
The following query reads process and system memory without changing settings. Use your own measurements for the conclusion. A low-pressure snapshot between jobs is useful context, but it doesn't certify headroom during the busiest hour.
SELECT @@SERVERNAME AS InstanceName,physical_memory_in_use_kb,
process_physical_memory_low,process_virtual_memory_low
FROM sys.dm_os_process_memory;
SELECT total_physical_memory_kb,available_physical_memory_kb,system_memory_state_desc
FROM sys.dm_os_sys_memory;Choose Stronger Separation When Required
Separate Windows machines or virtual machines fit workloads needing clearer capacity, maintenance, or failure boundaries. Several engines on one machine share a host outage and compete for its storage even with separate memory limits. Containers can provide repeatable isolation on supported SQL Server container platforms, but still share host resources.
Verify platform support explicitly. Don't assume a container is a supported replacement for a Windows engine installation.
Run several SQL Server instances together when the combined workload fits a monitored, owned budget. Keep endpoint, memory, CPU, and maintenance choices coordinated. Choose stronger separation when resource guarantees or independent failure handling require it.
Separate engine services are useful organizational boundaries. They don't create another memory bank or another processor just because the service list contains another line.
Related reading on this blog: Why 'Max Server Memory' Isn’t Always the Limit and Consolidating Small Instances Onto One Server.
A separate instance is not a separate machine, it is another workload sharing the same host.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
