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.

asked 19 Jul '11, 18:32

Jturner's gravatar image

Jturner
1816714
accept rate: 66%

edited 20 Jul '11, 08:54

Mark%20Culp's gravatar image

Mark Culp
23.3k9132275


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.

permanent link

answered 20 Jul '11, 07:38

John%20Smirnios's gravatar image

John Smirnios
9.0k377114
accept rate: 39%

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.

(20 Jul '11, 13:36) Jturner
Replies hidden
1

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)

(20 Jul '11, 13:58) John Smirnios

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

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

<g>

(20 Jul '11, 14:04) Breck Carter

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.

(20 Jul '11, 14:18) Jturner

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

(20 Jul '11, 14:18) John Smirnios

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

alt text

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.

permanent link

answered 20 Jul '11, 11:30

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 20 Jul '11, 11:31

3

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.

(20 Jul '11, 11:36) John Smirnios

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 :)

(20 Jul '11, 14:14) Breck Carter

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.

permanent link

answered 20 Jul '11, 07:37

Martin's gravatar image

Martin
8.6k118151237
accept rate: 14%

1

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.

(20 Jul '11, 09:27) John Smirnios
Replies hidden
1

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

(20 Jul '11, 09:44) Volker Barth
1

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

(20 Jul '11, 11:39) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×18

question asked: 19 Jul '11, 18:32

question was seen: 2,906 times

last updated: 20 Jul '11, 14:18