>>>>>>That link discusses setting affinity on W2K/W2K3/XP. I found the second while researching how to set it on Windows 7. While the author seems to be focused on performance, for this particular scenario (NTVDM.EXE) it would be used for reliability.
>>>>>>
>>>>>>Regarding processor affinity and performance, ISTR researching that some time ago (Web research, not testing) and the consensus was it makes things worse unless you have *very* specialized edge cases and you know exactly what you're doing. My guess is schedulers have only gotten better since then.
>>>>>
>>>>>quite true, but running several VM, each taxed with a vfp single thread core, might just sit on such an edge ;-))
>>>>
>>>>Hmm, that might be true on bare metal, but I'm not sure how you would implement it in a virtualized environment. Yes, you could set a process's affinity to a vcore in a VM, but nothing's stopping the hypervisor from swapping that vcore from one physical core to another, varying the (timesliced) portion of a real core that the vcore gets at a given time, etc. For best results you'd also want to set some sort of affinity in the hypervisor such that your VM's vcores were tied to specific, dedicated physical cores. That may be possible, but would violate pretty much every reason why people use virtualization in the first place ;)
>>>
>>>In our case the benefits of VM are mostly to have pathing in place as to not depend on dynamic pathing for each process and the ease of moving the environments to other HW in case of upgrade, failure or hosting switch. The 10% added runtime in the worst case was worth it, as fiddling with such issues was getting on my nerves.
>>>
>>>Yes, in my case the VMs would be set to specific cores - even if the speedup is only negligible, timing critical stretches should be less prone to variations stemming from the almost random distribution of others tasks swapping more or less than usual in the timed run.
>>
>>What would be more important to you - reduction in variation, or (incremental?) speed increase? Or both?
>>
>>On Hyper-V processor affinity isn't supported, but then they tell you how to do it anyways ;) :
http://blogs.technet.com/b/tonyso/archive/2009/09/22/hyper-v-how-to-set-processor-affinity-in-hyper-v.aspx>>
>>That post is 2009 but applies to Hyper-V in Server 2012 R2 as well.
>
>Brent Ozar, a SQL Server guru, preaches processor affinity (for all the cores) when running SQL Server on a VM (because you were forced to: he doesn't think it's a good idea to be running there if you want to scale).
I Googled [affinity site:brentozar.com] and didn't find any recommendations to that effect.
What I did find is
http://www.brentozar.com/archive/category/videos/page/4/ ("Don't Touch That Button!", most of the way down that page)(starting around 11:30). Same video at
https://www.youtube.com/watch?v=CsEsOO6EPNwHis staffer is basically recommending don't mess with it until all other tuning options are exhausted, and you've talked with MS support and they agree you're part of the 1% that can benefit.
That said, if by "scaling" he's talking about very large servers then he may have a point. My understanding is large systems start to use NUMA architectures i.e. certain processors can only physically access certain RAM memory banks. In cases like that you definitely wouldn't want to have data and working sets nicely cached in RAM in one memory bank, then have the processes accessing them swapped to another physical CPU socket. I believe modern OSs, Windows and otherwise are "NUMA-friendly" to a certain extent but for absolute maximum performance on large systems I can see where you might want to:
- Run on bare metal rather than virtualized
- Set affinities such that potential NUMA issues are avoided
I get the impression from other resources on his site that virtualization of SQL Server is an accepted thing, and he offers pointers on how to do it well.
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up