Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Instance won't start after changing Processor Affinity Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
I'll try setting it to default for memory allocation and see if that goes away.

I'm also going to try allocating just processor and not I/O affinity, as that was an earlier config that we were using but changed away from because we thought it might have been the cause of some performance issues.

I'll report back shortly with logs.
Post #1431710
Posted Friday, March 15, 2013 1:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
cryovenom (3/15/2013)
I'll try setting it to default for memory allocation and see if that goes away.


Will be interesting to see, but memory allocation is the one thing you do need to do on a server with multiple instances, leaving one or all at default's not a good idea, they will compete for memory and with the OS



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1431712
Posted Friday, March 15, 2013 1:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
My guess without being able to query the system is that you've mixed NUMA nodes in your settings. 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. The 6212 presents 2 NUMA nodes per socket, so you have 4 total NUMA nodes each with 4 cores and 8GB of RAM in your given configuration.

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


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1431724
Posted Friday, March 15, 2013 1:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
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.
Post #1431733
Posted Friday, March 15, 2013 2:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
cryovenom (3/15/2013)


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.


If you only have 2 nodes in 2008 you have different hardware. While the 2012 SQLOS memory manager was rewritten, the NUMA layout presented is from the hardware and SQLOS creates memory nodes that map to the hardware nodes directly, so you have to have different configurations for hardware.


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.


That is a dangerous and incorrect assumption. Just because SSMS does something one way doesn't mean that is the best, or even correct, way of doing things.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1431739
Posted Friday, March 15, 2013 2:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 4,472, Visits: 6,402
cryovenom: based on many things in your various posts you (and your company) REALLY REALLY need to do themselves a favor and get a good professional on board to help you get things set up optimally. You have made some VERY poor decisions so far, and if you continue down this path even if you get stuff working you will likely be very unhappy.

As an aside I am simply astounded that you ponied up the cost for Enterprise Edition and then got such a crappy piece of hardware to run your SQL Server(s) on!!! My LAPTOP will process data MUCH better than that "server" you bought!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431747
Posted Friday, March 15, 2013 2:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
In SQL Server 2008, SSMS didn't separate things into NUMA nodes at all. It just presented a list of Cores, 0 to 15, one column for Processor Affinity, one for I/O Affinity. In 2012 it breaks it into 4 NUMA nodes, but I didn't know what those were, so I just carried my settings over.

So on this server, the GTA instance (for example) has its processor affinity set on three cores in NUMA Node 2, and its I/O affinity set on a core in NUMA node 3. The ENT and DOCINDEX instances both use cores in NUMA Node 4.

If there are restrictions or limitations that SQL Server has related to which cores it can use across which nodes, I would like to know what they are. I just assumed that it was a hardware construct like "sockets" that it was exposing to me, but didn't think that it made a difference when it came to assigning affinity.
Post #1431748
Posted Friday, March 15, 2013 2:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 4,472, Visits: 6,402
Thought just occurred to me: have you checked the BIOS settings in both servers to see if there is a difference there that could be causing "identical" machines to not behave identically?? NUMA (or at least memory interleaving) may be disabled in the BIOS on one of them and not the other...

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431752
Posted Friday, March 15, 2013 2:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
TheSQLGuru (3/15/2013)
cryovenom: based on many things in your various posts you (and your company) REALLY REALLY need to do themselves a favor and get a good professional on board to help you get things set up optimally. You have made some VERY poor decisions so far, and if you continue down this path even if you get stuff working you will likely be very unhappy.

As an aside I am simply astounded that you ponied up the cost for Enterprise Edition and then got such a crappy piece of hardware to run your SQL Server(s) on!!! My LAPTOP will process data MUCH better than that "server" you bought!!


I realize that this is the internet, and that in all forums there's a tendency to make replies like this. But all your post does is denigrate my decisions, makes no attempt to educate me on which ones were bad and why, and also makes no attempt to help with the problem at hand. Then, for fun, it drives the point home with an exaggerated comparison and copious amounts of exclamation marks.

Yes, I'm not an expert. Yes, my company and I may not have made all the right decisions. I would much prefer to learn what's wrong, why, what do to going forward, and even maybe get some help with the actual problem as opposed to reading the kind of unhelpful and negative post you just made.

People aren't born experts, and often they get thrown into situations where they have to make decisions based on the information they can gather and the resources they have at hand. I came here to learn, troubleshoot, fix, and become better, not to be given crap.

Post #1431758
Posted Friday, March 15, 2013 2:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
TheSQLGuru (3/15/2013)
Thought just occurred to me: have you checked the BIOS settings in both servers to see if there is a difference there that could be causing "identical" machines to not behave identically?? NUMA (or at least memory interleaving) may be disabled in the BIOS on one of them and not the other...


This could definitely be the case. We had some BIOS issues with the machines when they came in and had to make changes to a few of them to get them working. I'm going to read up on NUMA (seeing as I know so little about it) and at my next maintenance window I'll compare BIOS settings between the two. Thanks!
Post #1431759
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse