• Best practice for MAXDOP is usually set to it to the number of physical cores in a NUMA node. How did you arrive at 5? Sorry if I missed something.

    When I had to tune some servers recently, I started taking measurements using this Paul Randal script[/url] and compared the waits before and after. CXPACKET went from being 25-35% of the waits to 6-7% of the waits. Now they're mostly backup IO and other background process related.

    Depending on the server, MAXDOP got either 4 or 6, and I ended up setting the cost threshold for parallelism to (mostly!) 100, but one SPED server got 200 because it's running 2008R2 Standard on almost EOL hardware with 16Gb of memory and it's about to get virtualized.