Just a short note – in SQL Server 2019, the setup process has a new MAXDOP tab:
I did a few quick test installs:
- 4 cores gave me a MAXDOP 4
- 8 cores offered MAXDOP 8
- 12 cores offered MAXDOP 6
Historically, the 0 default meant unlimited cores. I like this setting instead because if somebody amps up the VM’s core count later, MAXDOP won’t automatically rise to match. If you jump from, say, 4 cores to 8, then I still kinda like the 4 setting better.
In vaguely-related-news, this week’s CTP 3.0 adds a lot of diagnostics for Azure SQL DB Hyperscale, plus a new sp_configure option for hybrid_buffer_pool (no trace flag required.)