Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
IMO, the KB articles are very thorough and specific, so I would start with those recommendations. –  Jon Seigel Jan 7 '13 at 17:41

1 Answer 1

up vote 2 down vote accepted

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?

share|improve this answer
    
looking into sys.dm_os_nodes I see two nodes so each physical process on its own NUMA node. –  SQL Learner Jan 7 '13 at 18:00
    
Do you have node 0 and 1 or node 0 and 64? –  mrdenny Jan 7 '13 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 '13 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 '13 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 '13 at 14:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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