3

I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration I have question about setting MAXDOP however now a days what I find as a regular reader of this forum general consensus is that to leave server wide MAXDOP setting to 0 for tasks that need parallel operation (e.g. index rebuild and change cost threshold of parallelism to a higher number (15 or 20 instead of default 5).

However, this server has NUMA nodes and based on these MS support articles http://support.microsoft.com/kb/329204

http://support.microsoft.com/kb/2023536

to prevent costly foreign memory access it is recommended to set MAXDOP = number of cores in the physical processor.

This server has 2 physical processors with 16 logical (8 physical) cores on each socket. Does guideline suggested by that MS support KB is still good?

I know everything need to be tried and tested but my goal is to set MAXDOP to the best theoretical number and adjust it if needed later on. Based on it should I set MAXDOP setting to 8?

Improve this question
1
  • 3
    IMO, the KB articles are very thorough and specific, so I would start with those recommendations.
    – Jon Seigel
    Jan 7, 2013 at 17:41

1 Answer 1

Reset to default
4

Typically I recommend to my clients leaving the MAXDOP alone. SQL Server is NUMA aware so it knows which NUMA node the processors are in and which NUMA node the memory is in and it'll do it's best to assign the work to a scheduler on the correct NUMA node for the data that you are looking for.

Looking into this sort of stuff also requires knowing how many NUMA nodes you have. Is each physical processor in its own NUMA node or are they node in the same NUMA node?

Improve this answer
5
  • looking into sys.dm_os_nodes I see two nodes so each physical process on its own NUMA node.
    – SQL Learner
    Jan 7, 2013 at 18:00
  • Do you have node 0 and 1 or node 0 and 64?
    – mrdenny
    Jan 7, 2013 at 20:36
  • I have node 0, node1 and node id 64 for DAC. Denny, I think your suggestion for MAXDOP=0 may not be good as I never read in the books or cindy gross blog here blogs.msdn.com/b/cindygross/archive/2011/01/28/… setting MAXDOP to more than 8. Right now with MAXDOP=0 it can possibly use all 32 schedulers.
    – SQL Learner
    Jan 7, 2013 at 22:16
  • 1
    It all depends on the workload of your system. When reading Cindy's blog (or really anyone that works on the CAT team) they deal with the biggest most complex systems and their recommendations will typically be based on that. I've got customers with 64 cores, 4 NUMA nodes, 73 schedulers and maxdop set to 0 and everything is working just fine. It's all about how the system works and remember that there's no hard rules for any of these settings. Every system is different. Start somewhere and test under your real world workload.
    – mrdenny
    Jan 9, 2013 at 0:51
  • Thanks Mr. Denny. few years back your storage basics for DBAs PASS summit presentation was awesome. Thank you.
    – SQL Learner
    Jan 9, 2013 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.