Setting up a new server

• richardmgreen1

  SSChampion

  Points: 10356

  More actions

  July 11, 2016 at 1:59 am

  #328112

  Hi all

  We've just had a new server set up (as a VM) and I'm just wanting to check some settings.

  The server is running SQL2012 BI edition and has 32GB RAM.

  I've assigned 28GB to SQL to leave 4GB for the OS and other bits and pieces.

  Now for the odd part.

  The server has 16 CPUs (AMD Opteron Processor 6334 according to device manager) and looking at the server properties in SQL they've been put into 3 NUMA nodes.

  Node 0 has CPUs 0-5

  Node 1 has CPUs 6-11

  Node 2 has CPUs 12-15

  I've set the Max degree of Parallelism to 6 (assuming I've read Brent Ozar's post correctly from https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/)

  I'll tinker with the Cost Threshold for Parallelism setting as time goes by but the above is the one I want to get right to start with.

  Can someone please advise if I've set it correctly?

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  July 11, 2016 at 8:49 am

  #1889433

  Quick though, NUMA node 2 only has 4 cores, possibly that should be your maxdop limit.

  Depending on the type of workload I normally set the Cost Threshold for Parallelism to 30-50 and tinker from there.

  😎