SQL Server 2012 EE Configuration

Question

SQL Server 2012 EE Configuration

Ford Fairlane

SSCertifiable

Points: 7664
More actions
January 13, 2014 at 11:01 pm

#288323

Hi All,
Just looking at what your thoughts are...
I have a couple physical boxes, each has 32 CPUs, 256GB RAM and some other fruit, ie SSD etc. Now I am going to run multiple instances on these and was wondering what your thoughts would be on CPU affinity. Would you split the CPU between the different instances or let SQL sort it out itself.
Most of these systems will be running mining applications and some of them can be very resource intensive. Large data loads and dealing with very large GIS modeling files. Other will be small and not be much of an issues so all in all a nice mix of systems.
TIA.
Hope this helps...
Ford Fairlane
Rock and Roll Detective

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

In my opinion, yes, I would set affinity to the available NUMA nodes to avoid overlap between the instances if they are all high workload at the same time.

However that would mean you are not utilising all your available CPUs if only a single instance is under high workload. If you want to use all available resources on demand then Windows System Resource Manager is probably the way to go.

Do you need multiple instances? You might be able to use SQL resource governor which will also allow a single workload to use 100% cpu as required or less when 2 workloads are competing.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 2

Unfortunate I need multiple instances due the separation of certain applications and the ability to control them patch them differently.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

I'd probably try to isolate the big systems to a single box each and then cram all the small systems onto one box and let SQL fight it out there. But, if you're running out of boxes and must still have instances, yes, I'd isolate the CPU and limit the memory available to each instance.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning