Assign dbsrv 12 to a set of CPUs (affinity mask?)

[Former Member]

I want to assign which CPUs a dbsrv12 engine will run on. I will be running 28 versions of dbsrv in a single VM 14 gig of memory 12? processors on Window Server 2008 64bit. I was hoping to distribute the workload across the available CPUs, all i have found so far is the ability to limit the number of CPUs an instance will be able to use.

Has anyone tried this before?

This is a testing/development environment with over 90 databases being served to meet different needs. The number of servers running reflects the need to refresh on request, the number reflects the logical grouping that could be stopped with out effecting all ongoing testing. I can have more VMs if it would be better but there is a limit to the number.

[johnsmirnios · Accepted solution]

There is currently no facility for setting a specific affinity mask for the server though I think it is an excellent product improvement request. Although it would be relatively easy to let users specify a set of logical processors to use, users are not always aware of which logical processors are threads of the same core, etc so some thought would need to be put into how users would want to express the processor bindings that they want.

[Former Member · Accepted solution]

Can anyone comment on how SA will use the cores available when the services are started. Will it allways start with core 1 or will it spread itself across the available set evenly? I would assume that when no affinty mask is provede at startup, it is up to the OS to decide where a process will be started. If this is the case some distribution may occur.

[johnsmirnios · Accepted solution]

It always starts at (package 0, core 0, thread 0) and then spreads "evenly" from there. No server has any knowledge about how other servers have bound their threads. Here, btw, "evenly" means sorting the available (pkg,core,thread) ids in "thread,core,package" order. For example, on a 2-cpu system where each cpu has 4 cores with 2 threads per core, the logical cpus will be chosen in the following order: (0,0,0),(1,0,0),(0,1,0),(1,1,0),(0,2,0),(1,2,0),(0,3,0),(1,3,0),(0,0,1),(1,0,1),(0,1,1),(1,1,1),(0,2,1),(1,2,1),(0,3,1),(1,3,1)

[Breck_Carter · Accepted solution]

(you,live,for),(this,stuff,don't)(you,eh,John?)

(hey,did,I),(just,invent,a),(new,haiku,form?)

<g>

[Former Member · Accepted solution]

John based on you answer even if the OS decides to start the service on core 5 the service will start its threads starting on core 0. Thanks for the information. I have no real power to control anytbing in this area so I will just have to live with the results. My thinking now is to use 3 VMs each running 10 services.

[johnsmirnios · Accepted solution]

(and,I,get),(paid,$,$),(for,it,too)!

[jeff_albion]

There is the "Set Affinity..." feature of Windows Task Manager:

But this only works for processes that have already been started (i.e. not very useful for services) and does not persist over system reboots.

Beyond that, you might find this (non-affiliated) blog post helpful, if you'd like to use third-party scripting tools. I won't comment on the usability of these suggestions for running system services - you would have to test their functionality first.

[johnsmirnios]

I do not recommend this approach since there are other server components that will not be configured correctly. For example, various threads in the server also set their thread affinity masks so that we have better control over where tasks are run (you want tasks to prefer to stay on the same processor so that code & data caches on the CPU are more likely to be useful). We also use IO completion ports which have a degree of parallelism associated with them, etc.

[Breck_Carter]

IMO there's no real reason to down-vote Jeff's answer into negative territory... THAT should be saved for the occasional complete jerk, and Jeff is certainly not one of those. This forum ain't StackOverflow where rudeness reigns so supreme it has lost its bite 🙂

[MCMartin]

I think you should be able to limit the VM to a specific set of CPUs, so you don't have to worry about SQLA itself.

[johnsmirnios]

The user wants to run multiple SA servers in a single VM with many processors and have each SA server limited to a subset of processors. Running multiple VMs to accomplish the same thing is a large resource & management overhead and possibly a financial overhead as you may need to buy an OS license for each VM.

[VolkerBarth]

...though running many database servers instead of some (with the same total number of databases) may lead to a resource overhead, too... - OTOH, it will isolate databases from different customers better.

[johnsmirnios]

Agreed but as soon as you have two SA servers running, you may very well want to restrict them to particular processors.