Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2012 » SQL 2012 - General » Instance won't start after changing Processor...
Add to briefcase
32 posts, Page 3 of 4
«««1234»»

Instance won't start after changing Processor Affinity Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jonathan Kehayias
Posted Friday, March 15, 2013 2:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
Post the results of the following two queries from BOTH servers for each instance:

select name, value, value_in_use 
from sys.configurations
where name = 'affinity mask';

select 
	dos.scheduler_id,
	dos.cpu_id,
	dos.is_online,
	don.cpu_affinity_mask,
	don.online_scheduler_count,
	don.online_scheduler_mask,
	don.memory_node_id
from sys.dm_os_schedulers as dos
inner join sys.dm_os_nodes as don
	on dos.parent_node_id = don.node_id
where dos.status = 'visible online';

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 #1431761
Jonathan Kehayias
Posted Friday, March 15, 2013 3:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
cryovenom (3/15/2013)
Then, for fun, it drives the point home with an exaggerated comparison and copious amounts of exclamation marks.


It actually isn't an exaggerated comparison at all. My Dell Precision M6700 laptop would outperform your server head to head. My M6700 has the i7-3940XM, 32GB RAM and 1.25TB of SSD storage and scores over 16000 for a GeekBench benchmark, where a AMD 6212 system scores in the 12000 range.


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.


This was exactly what the recommendation was geared at, you've just taken it as a personal attack. Above you admit that you aren't an expert here and some decisions may not have been the right ones. Bringing an industry expert in can help you understand the what and why and can be the fastest way to solve your problem while also obtaining knowledge transfer. I used to bring consultants in as a DBA all the time because no one can be an expert in everything and having the right resource available prevents problems from occurring. If you are dedicated to doing it on your own, you have to expect that at some points people are going to point out mistakes you are making or have made, and that you might hit a limit to what information is practical to put into a forum thread. I used to write multi-page explanations of SQL Server internals on forum threads, I won't do that anymore because it requires to much time.

The configuration you are trying to obtain doesn't make sense. With 4 NUMA nodes and 4 instances I would just use ALTER SERVER CONFIGURATION and set the process affinity for each instance to a specific node. The theory behind reserving CPU 0 and 15 for the OS is good in theory, but not in the reality of how OS scheduling occurs or other process scheduling is going to occur. In all my time working with SQL Server I've only once had to reserve CPUs for the OS and it was for a separate application that was running on the server and it had it's own process affinity settings to make it run on those CPUs. You are over complicating things here for no real net gain other than you promised something that I wouldn't have recommended promising.

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 #1431772
cryovenom
Posted Friday, March 15, 2013 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
Production Server\NCR:

affinity mask 14 14

1 1 1 15 3 14 0
2 2 1 15 3 14 0
3 3 1 15 3 14 0

Development Server\NCR:

affinity mask 14 14

1 1 1 15 3 14 0
2 2 1 15 3 14 0
3 3 1 15 3 14 0

Production Server\GTA:

affinity mask 224 224

5 5 1 240 3 224 1
6 6 1 240 3 224 1
7 7 1 240 3 224 1

Development Server\GTA:

affinity mask 224 224

5 5 1 240 3 224 1
6 6 1 240 3 224 1
7 7 1 240 3 224 1

Production Server\ENT:

affinity mask 3584 3584

9 9 1 3840 3 3584 2
10 10 1 3840 3 3584 2
11 11 1 3840 3 3584 2

Development Server\ENT:

affinity mask 3584 3584

9 9 1 3840 3 3584 2
10 10 1 3840 3 3584 2
11 11 1 3840 3 3584 2

Production Server\DOCINDEX:

affinity mask 8192 8192

13 13 1 61440 1 8192 3

Development Server\DOCINDEX:

affinity mask 8192 8192

13 13 1 61440 1 8192 3


And this is with the settings where if I stop and restart GTA or DOCINDEX on the Development Server, it will not start.
Post #1431781
TheSQLGuru
Posted Friday, March 15, 2013 3:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 3,572, Visits: 5,106
In my experience the best way BY FAR to become an expert (certainly at such a complex system as SQL Server) is to be MENTORED by an expert. So I stand by my recommendation 100%. Your company would come out with an optimal environment and you would learn along the way. Win-win.

Oh, I did think of another thing, although it is reaching a bit: maybe try reseating the memory. I have experienced or heard about some very weird things happening with poorly-seated memory.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431784
cryovenom
Posted Friday, March 15, 2013 3:15 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)


The configuration you are trying to obtain doesn't make sense. With 4 NUMA nodes and 4 instances I would just use ALTER SERVER CONFIGURATION and set the process affinity for each instance to a specific node. The theory behind reserving CPU 0 and 15 for the OS is good in theory, but not in the reality of how OS scheduling occurs or other process scheduling is going to occur. In all my time working with SQL Server I've only once had to reserve CPUs for the OS and it was for a separate application that was running on the server and it had it's own process affinity settings to make it run on those CPUs. You are over complicating things here for no real net gain other than you promised something that I wouldn't have recommended promising.

[/quote]

Makes sense. 4 instances, 4 NUMA nodes, I'll give it a shot and see if that works on DEV while I'm researching a better solution long-term, be it virtualization, Windows System Resource Manager, or something else.

And I'll still check BIOS settings between the two boxes at my next maintenance window just to make sure.
Post #1431787
cryovenom
Posted Friday, March 15, 2013 3:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
TheSQLGuru (3/15/2013)
In my experience the best way BY FAR to become an expert (certainly at such a complex system as SQL Server) is to be MENTORED by an expert. So I stand by my recommendation 100%. Your company would come out with an optimal environment and you would learn along the way. Win-win.


I enjoy mentoring under people with more experience, and I don't disagree with your recommendation. Trying to sell it to management is always another story. It would take too long here to get into details, but suffice it to say that convincing management to do anything from sending you on a day course, to actual training, to bringing in outside experts is a difficult proposition at best, so I make do with what I've got. Booksonline, friends in the industry, and whatever research I can do on the internet or at the bookstore.

TheSQLGuru (3/15/2013)

Oh, I did think of another thing, although it is reaching a bit: maybe try reseating the memory. I have experienced or heard about some very weird things happening with poorly-seated memory.

[/quote]

I've noticed that memory keeps getting mentioned along side the NUMA nodes and cores. I haven't read up yet, but are these things connected? Could a memory issue or configuration cause problems with setting core preference? I really have to take a quick few minutes to google what the heck a NUMA node is.
Post #1431798
cryovenom
Posted Friday, March 15, 2013 3:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 18, 2013 6:16 AM
Points: 16, Visits: 12
And less than two minutes later I realize how silly my last post seems.

For anybody finding this thread through google, NUMA (or Non-Uniform Memory Access) definitely has a lot to do with both. See here for a start:
http://msdn.microsoft.com/en-ca/library/ms178144%28v=sql.105%29.aspx
Post #1431801
Jonathan Kehayias
Posted Friday, March 15, 2013 3:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742
Pull the cover on the Dev box and look at where the RDIMMs are located and see if they are all populated on one socket or balanced across both of them.

To get started on learning about NUMA:

http://www.sqlskills.com/blogs/jonathan/understanding-non-uniform-memory-accessarchitectures-numa/

and then read the following and all the links at the bottom of it:

http://blogs.msdn.com/b/psssql/archive/2011/11/11/sql-server-clarifying-the-numa-configuration-information.aspx

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 #1431804
Lynn Pettis
Posted Friday, March 15, 2013 3:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 21,588, Visits: 27,384
cryovenom (3/15/2013)
And less than two minutes later I realize how silly my last post seems.

For anybody finding this thread through google, NUMA (or Non-Uniform Memory Access) definitely has a lot to do with both. See here for a start:
http://msdn.microsoft.com/en-ca/library/ms178144%28v=sql.105%29.aspx


To make this one easier: http://msdn.microsoft.com/en-ca/library/ms178144%28v=sql.105%29.aspx


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431805
TheSQLGuru
Posted Friday, March 15, 2013 3:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 3,572, Visits: 5,106
Given the hardware budget squeeze I didn't expect "management" to understand the real value of a good consultant/mentor. Damn shame is about all you can say to that.

Good luck finding much useful guidance on SQL Server and NUMA. There is a dearth of good information out there, especially deep/technical guidance, although it is certainly better than it used to be. IIRC there was a pretty good session at one of the SQL Bits conferences that you might be able to download.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431807
« Prev Topic | Next Topic »

Add to briefcase
32 posts, Page 3 of 4
«««1234»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.