SQL Server MaxDop Settings – Multi-instance Standard Edition Configuration

configurationmaxdopsql server

You read the title and are still confused? Well I am too.

I've just started a new job as DBA for a new employer and have come across some creative ways of installing SQL Server. My previous experience with SQL Server is all based on single MSSQLSERVER instances running on either virtual or physical hardware. We used to avoid multi-instance installations of SQL Server just to keep everything really separated and simple.

Here at my new employer they have bunched quite a few instances of SQL Server Standard Edition on one piece of virtual hardware. Their (well I guess I should now call it, our…) reasoning:

Having multiple instances of SQL Server on one piece of (virtual) hardware reduces the amount of SQL Servers and license costs in the whole environment.

I haven't found any other reasoning behind such a configuration yet.

There are absolute no availability groups or transactional replication going on and transaction log shipping has not been implemented.

The servers have been configured to have the default instance and multiple additional instances as explained below.

SQL Server Environment

The SQL Servers are configured to contain multiple instances.

Server to instance relationship

Each SQL Server can have 1 to n instances

SQL_SERVER_01 (Standard Edition SQL Server)
\ MSSQLSERVER (default instance)
\ VARIOUS_INS (the 2nd instance)
\ SOMETHINGNW (the 3rd instance)
\ A_NAMEGIVEN (the 4th instance)
\ INSTANCENEW (the xth instance)

Instance | IP | Port | Alias (CNAME)

Each instance is related to an IP address and each IP address has an individual Alias (CNAME) so that the SQL Server can always listen on port 1433. This simplifies the firewall configuration as the rules only have to be added for the default SQL Server port. Hmmm.

MSSQLSERVER | 10.0.0.22 | 1433 | SQL_SERVER_01_I00
VARIOUS_INS | 10.0.0.23 | 1433 | SQL_SERVER_01_I01
SOMETHINGNW | 10.0.0.24 | 1433 | SQL_SERVER_01_I02
A_NAMEGIVEN | 10.0.0.25 | 1433 | SQL_SERVER_01_I03
INSTANCENEW | 10.0.0.26 | 1433 | SQL_SERVER_01_I04

So for each SQL Server instance running on the same virtual server the network team has to provide an IP address for the virtual NIC and create a CNAME/Alias for the instance's IP address. Each virtual NIC has to be configured for the correct IP address and the SQL Server Configuration has to be correctly configured for each instance's IP address (listen on this IP address, active for this IP address, ….). The SQL Server will not respond to the typical SERVER\INSTANCE notation, which means the servers are only reachable via the Alias/CNAME (e.g. SQL_SERVER_01_I00)

The (virtual) hardware

Before I forget, I think it might be a good idea to give you an idea of the typical virtual hardware configured for such a bunch of SQL Server instances.

Disks

The virtual disks are pre-configured in VMware and attached to the SQL Server. Some hardware vendor in the background. Could be IBM, could be Hitachi, …. A disk for the MDF files and a disk for the LDF files.

Processors

Yes, multiple processers. In this example four logical processors @2.9 GHz

Memory

A mere 32GB for this server. Each SQL Server instance is configured to consume between 1GB and 4GB of memory. This server has for example 6 instances, each containing between 1 … 10 databases varying in size from a few 100 MBs to a couple of GBs. Nothing massive.

SQL Server Instance Configuration

Each SQL Server instance will be configured as follows.

max degree of parallelism

Default (0)

Memory

Min Memory will be set to 256 MB and Max Memory to between 1GB and 4GB.

Affinity Mask

Not configured.

cost for max degree of parallelism

Default (5)

My thoughts

From my experience, I learnt that having single instances is the best regarding configuration settings and when analysing issues. But this doesn't seem to be an option here. So no need to start discussing in that direction. I know.

I figure that having only 4 logical processors and MAX_DOP set to 0 for all seven instances and the multiple databases in the background is a bad idea. If one system lags, then they are all going to lag seriously.

The question(s)

Seeing as you got to know my environment, I guess somebody out there will have a similar configuration and will be able to provide me with some script to analyse everything or be able to point me in the right direction towards recommendations.

Here goes:

Should I have at least one logical processor per instance, given that I currently only have 4 logical processors for 6 instances and MAX_DOP set to 0?
If I had one logical processor per instance, should I leave MAX_DOP at 0 or limit each instance to MAX_DOP = 1?
Seeing as it is a Standard Edition should I limit MAX_DOP to 4 as an alternative?

I'm not in a hurry and I do have some time to spend. I'm just curious if anybody has been in the same situation as myself and how you/they came to deal with the situation.

Thanks for your time.

Best Answer

1. Should I have at least one logical processor per instance, given that I currently only have 4 logical processors for 6 instances and MAX_DOP set to 0?

That depends, how much CPU usage is each instance using on average? You can get this information from the default health session extended event that is running (assuming 2008+).

Four logical processors may be perfectly adequate for this workload - we won't know until we have data. Having said that, since each instance of SQL Server acts on its' own and has no idea of the other instances installed, I'm sure Windows isn't too happy about swapping threads.

I'd take a look at my wait stats dmv and see if we have a higher percentage of wait time on the signal_wait part which would indicate scheduling issues and possible contention between instances. Additionally I'd trend this with the cpu context switches to see if there is a weak or direct correlation with "bad times" and "good times" of instance health.

My gut, though, tells me that unless these are woefully underutilized instances that this server is bound for issues - whether they are happening now or in a few months from now.

2. If I had one logical processor per instance, should I leave MAX_DOP at 0 or limit each instance to MAX_DOP = 1?

MAXDOP just limits the number of logical processors ('schedulers') that a single parallel query may use during execution. There is nothing stopping SQL Server from running multiple parallel queries; in fact I've diagnosed and fixed this very issue a multitude of times for places that didn't even know they had a scheduling problem (it was looked at as a "blocking" problem).

Setting MAXDOP to 1 essentially makes all user queries single threaded. This, again, doesn't stop SQL Server from executing more than a single task at a time as it merely forces serial executions. This means each instance has four (4) visible online schedulers for a total of 4*x number of instances possible concurrent queries. Setting MAXDOP here doesn't solve the issue of being logically overburdened, not taking into account the actual setting of the virtual server as to whether or not it can run on hyper threaded (if enabled) cores. Yikes.

I'm not saying forcing MAXDOP to 1 is good or bad, just that we have no data to go on. Thus we won't know the impact of the setting. Again, though, I'd DEFINITELY not have it set to four (4)!

3. Seeing as it is a Standard Edition should I limit MAX_DOP to 4 as an alternative?

Well, it can't be higher than four (4), so it's already limited. This question, I believe, is closely coupled with the question above. Hopefully I've explained it to a satisfactory level of understanding.

If you want to get an idea (due to the small threshold for parallelism) of how many queries are being parallelized, you can check the execution plans dmv. This is a fairly expensive operation, so please do it off hours or times of little to no usage between ALL of the instances. Remember, what you do to one instance is going to be (roughly) done to all because of the shared nature of the server.

Related Solutions

Sql-server – Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster

You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event using a startup procedure. I blogged about this here, but for a different reason (failing over to a node with a different amount of memory):

https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

Basically, you just need to check if both instances are on the same node (and this will require a linked server to be set up in both directions), and adjust accordingly. A very quick and completely untested example based on my blog post and assuming there is only one instance on each node at a time presently (the question is a bit ambiguous if you have 2 total instances or 4):

CREATE PROCEDURE dbo.OptimizeInstanceMemory
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
     @thisNode      NVARCHAR(255) = CONVERT(NVARCHAR(255),
                                  SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
     @otherNode     NVARCHAR(255),
     @optimalMemory INT = 12288, -- 12 GB
     @sql           NVARCHAR(MAX);

  SET @sql = N'SELECT @OtherNode = CONVERT(NVARCHAR(255), 
                        SERVERPROPERTY(N''ComputerNamePhysicalNetBIOS''));';

  EXEC [SERVER\INSTANCE].master..sp_executesql @sql, 
    N'@OtherNode NVARCHAR(255) OUTPUT', @OtherNode OUTPUT;

  IF @thisNode = @otherNode
  BEGIN -- we're on the same node, let's make everyone happy
    SET @optimalMemory = 6144;
  END

  SET @sql = N'EXEC sp_configure N''max server memory'', @om;
    RECONFIGURE WITH OVERRIDE;';

  EXEC                   master..sp_executesql @sql, N'@om INT', @optimalMemory;
  EXEC [SERVER\INSTANCE].master..sp_executesql @sql, N'@om INT', @optimalMemory;
END
GO

EXEC [master].dbo.sp_procoption 
  N'dbo.OptimizeInstanceMemory', 'startup', 'true';

Of course create it again on the other instance, swapping the linked server name used.

This gets a little more complex if you have to adjust depending on whether you are sharing the current node with 1, 2 or 3 other instances.

Note that this will cause other side effects such as clearing the plan cache (in the event when one of the instances didn't just restart or fail over, in which case the plan cache would be empty anyway), but these are arguably better than leaving both instances to assume they still have 12 GB of memory to play with - there will be a lot of thrashing if they're both heavily used.

You may also want to consider other options such as global maxdop, NUMA/CPU affinity etc. depending on how sensitive the system is to the amount of resources available.

Sql-server – SQL Server R2 Standard Edition MAXDOP setting

Standard edition will not generate a parallel plan that uses more than 4 processors i.e. MAXDOP 4. Setting MAXDOP to a value higher than this will not alter the limitation imposed by the edition. You get no warning or error when setting the higher value, it will be accepted.

SQL Server Environment

Server to instance relationship

Instance | IP | Port | Alias (CNAME)

The (virtual) hardware

Disks

Processors

Memory

SQL Server Instance Configuration

max degree of parallelism

Memory

Affinity Mask

cost for max degree of parallelism

My thoughts

The question(s)

Best Answer

Related Solutions

Sql-server – Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster

Sql-server – SQL Server R2 Standard Edition MAXDOP setting

Related Question