Jonathan Kehayias (3/15/2013)
My guess without being able to query the system is that you've mixed NUMA nodes in your settings.
This may sound like a rather basic question, but what do you mean by "you've mixed NUMA nodes in your settings"? Can two instances not use cores in the same NUMA node? or can one instance not use cores in separate NUMA nodes? I noticed that in 2012 it separated my cores into 4 nodes, while 2008 didn't do that, but I didn't realize there were things I couldn't split across nodes.
Jonathan Kehayias (3/15/2013)
The AMD 6212 would have been my last choice for a SQL Server processor today, the single threaded performance of those is substantially lower than the current Intel E5 series processors.
That was purely a cost restriction. We were able to get Supermicro servers with 2x Opteron 6212s, 32GB of RAM and 6x240GB SSDs, with hotswap PSUs, hotswap drive bays and Adaptec RAID cards for under $4k ea, taxes inc. We couldn't find anything close on the Intel side, or through large vendors like HP, Dell, IBM, etc... So we built these whitebox servers. They're way more performant than we even need, considering that each of these instances used to be a server with 2x single-core Opteron 2000-series processors and 4GB of DDR2 on Tyan boards in Antec cases with no hotswap anything and Seagate 7,200rpm drives. We're happy with the price/performance ratio of these new boxes and have no complaints about the Opterons
Jonathan Kehayias (3/15/2013)
I don't know why you are using affinity mask settings here which are deprecated, instead of using ALTER SERVER CONFIGURATION (http://msdn.microsoft.com/en-us/library/ee210585.aspx) which is how you should be setting your process affinity per instance in SQL Server 2012.
I was setting them in SSMS. The only reason that chunk of SQL using sp_configure 'affinity mask' appears there is because that's what I found when I was looking for a way to un-do the setting without an uninstall/reinstall of the instance. I'm assuming SSMS uses ALTER SERVER CONFIGURATION behind the scenes to set things.
Jonathan Kehayias (3/15/2013)
As Gail already pointed out, I wouldn't recommend setting a server up in the manner that you are doing here. If you want isolated workloads, make that server a VM host and run 4 different VMs so you have the type of isolation you are trying to get here.
I appreciate the recommendation. Adding VM licensing and three more OS licenses to the mix is not something I will easily be able to sell management on, and our chosen VM host (VMWare ESXi 5) has a bit of trouble with the RAID cards on these servers so I'd likely have to hack something together by installing an OS and running virtualization software within the OS, and lose a bunch of resources to overhead along the way. The Windows System Resource Manager that Gail mentioned seems like a much more attractive option if that results in similar resource isolation guarantees.
And as I said, until I get that working, I'd like to solve this mystery if possible.