Quick Scan Report – More Than One Instance

What this check looks for

More than one SQL Server instance installed on the machine, read from the registry. It needs sysadmin to read that, and it is skipped on Amazon RDS.

Why it matters

Each SQL Server instance believes it is alone on the machine. Nothing tells any of them otherwise, and by default each one will try to take everything.

Memory is the sharp edge. With max server memory at its default, an instance will grow its buffer pool until Windows signals memory pressure. Two instances doing that compete, and the outcome is decided by which one happened to be busier first. The consequences:

• Windows starts paging, which is catastrophic for a database server.
• One instance is starved, and it is not necessarily the unimportant one.
• A restart changes the balance, so the behavior is not even stable. The instance that restarts last gets what is left.
• Under real pressure, Windows may trim SQL Server’s working set, which produces sudden and dramatic performance loss that is hard to attribute.

CPU is shared with no priority. Every instance schedules against the same cores, so a runaway query on the development instance takes CPU from production. Nothing in SQL Server arbitrates between instances.

And so is storage, which is often the real bottleneck. Two instances writing their transaction logs to the same volume interleave sequential writes, and each one’s commit latency is the other’s problem.

The operational costs are just as real:

• Patching is coupled. Instances on one machine share the Windows patching schedule and the reboot. You cannot take an outage on the development instance without taking one on production.
• A crash takes everything. A hardware fault, a full system drive or a Windows problem takes all instances at once, so the blast radius of any incident is multiplied.
• Licensing. On per core licensing, the cores are licensed for the machine, so stacking can be economical. On a Standard Edition instance limited to 24 cores, stacking several instances on a 32 core machine means each is separately capped, which is often not what anybody intended.
• Troubleshooting is harder, because a performance counter or a Task Manager reading covers the whole machine rather than the instance you care about.

Stacking is not automatically wrong. It is a reasonable way to consolidate small workloads, to keep an old version alongside a new one during a migration, or to separate two applications that need different collations or different service accounts. What makes it wrong is doing it without configuring for it, which is the usual case and what this finding is really about.

How to confirm it yourself

Which instances exist on the machine:

EXEC xp_instance_regread
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
     N'MSSQLSERVER';

The full list is easier to read from PowerShell on the server:

Get-Service | Where-Object { $_.Name -like 'MSSQL*' } | Select-Object Name, Status, DisplayName

What this instance thinks it can have:

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('max server memory (MB)', 'min server memory (MB)',
                  'max degree of parallelism', 'affinity mask');

max server memory (MB) at 2147483647 is the default, which means “take everything”. On a stacked machine that is the finding.

How much memory the machine has and what this instance is actually using:

SELECT [physical_memory_kb] / 1024               AS [server_total_mb],
       [committed_kb] / 1024                     AS [this_instance_committed_mb],
       [committed_target_kb] / 1024              AS [this_instance_target_mb],
       [cpu_count]                               AS [cores],
       [sqlserver_start_time]
  FROM sys.dm_os_sys_info WITH (NOLOCK);

Whether Windows is under pressure:

SELECT [total_physical_memory_kb] / 1024   AS [total_mb],
       [available_physical_memory_kb] / 1024 AS [available_mb],
       [system_memory_state_desc]
  FROM sys.dm_os_sys_memory WITH (NOLOCK);

system_memory_state_desc reading anything other than “Available physical memory is high” on a stacked machine means the instances are already competing.

And whether this instance has been trimmed, which is the symptom of losing that competition:

SELECT [physical_memory_in_use_kb] / 1024  AS [working_set_mb],
       [large_page_allocations_kb] / 1024  AS [large_pages_mb],
       [locked_page_allocations_kb] / 1024 AS [locked_pages_mb],
       [process_physical_memory_low],
       [process_virtual_memory_low]
  FROM sys.dm_os_process_memory WITH (NOLOCK);

process_physical_memory_low of 1 means Windows has asked SQL Server to give memory back.

Run these on every instance, not just this one. The picture only makes sense across all of them.

How to fix it

The fix is to divide the machine explicitly instead of letting the instances fight over it.

1. Set max server memory on every instance, and make the total leave room for Windows. This is the single most important change:

-- example: 64 GB machine, two instances
-- leave 8 GB for Windows, give production 40 GB and development 16 GB
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 40960;
RECONFIGURE;

Budget it deliberately: total machine memory, minus 4 to 8 GB for Windows, minus anything else running on the box, divided between the instances in proportion to their importance. The sum of every instance’s max server memory must be less than that.

Set min server memory too, which is unusual advice and correct here. On a stacked machine it stops a quiet instance being squeezed to nothing by a busy one, so that when it does get traffic it is not starting from an empty cache:

EXEC sp_configure 'min server memory (MB)', 8192;
RECONFIGURE;

2. Set MAXDOP on each instance, so one instance’s parallel query does not take every scheduler:

EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

3. Consider CPU affinity only if one instance genuinely must be protected from another. Partitioning cores between instances gives hard isolation and gives up the ability to use idle capacity, so it is a trade rather than an improvement. Most stacked machines are better served by memory limits alone.

4. Separate the storage. Give each instance its own volumes for data, log and tempdb where you can. Sharing one log volume between instances is the most common avoidable I/O problem on a stacked machine.

5. Give each instance its own service account. It makes permissions auditable, it makes failures attributable, and it lets you restrict what each instance can reach.

6. Prioritize the production instance explicitly, in memory allocation, in storage placement, and in your patching order.

And revisit whether they should be stacked at all. Virtualization gives you the isolation that stacking does not: independent restarts, independent patching, independent resource limits, and a failure that takes one workload instead of all of them. If the reason for stacking was hardware cost, the reason has usually expired.

How long it takes

About an hour to budget the memory and apply the settings across the instances. Separating storage or moving an instance to its own machine is a project.

Related reports

Related checks

Frequently asked questions

Is running multiple instances always bad? No. It is a reasonable consolidation for small workloads or for keeping versions side by side. What makes it a finding is leaving every instance configured as though it were alone.

How should I split the memory? Total memory, minus 4 to 8 GB for Windows, divided between instances by importance. The sum of all max server memory values must be below that figure, and every instance must have one set.

Should I use CPU affinity? Usually not. It gives hard isolation and gives up the ability to use idle cores. Memory limits solve most of the problem; reach for affinity only when one instance must be protected absolutely.

Is it cheaper than separate servers for licensing? On per core licensing the cores are licensed once for the machine, so it can be. Check that the edition core limit does not mean each instance is separately capped below the machine’s capacity, which is a common surprise.