When configuring SQL Server on a VM does the recommendation around tempDb and MAXDOP still stand true?

TempDB - As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors.

MAXDOP - Server with multiple NUMA nodes > Less than 8 logical processors > Keep MAXDOP at or below # of logical processors.

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

I have a VM with the following and 2016 SP1 installed so my intention is to config 2 TempDb files and set MAXDOP = 2?

enter image description here

  • 1
    When you install SQL Server 2016+, tempdb should be automatically configured for you during setup according to the best practices you referenced. Was it not, or have you not installed it yet? May 16 '18 at 13:05
  • Not installed yet. So the fact it's a VM doesn't make any difference? Is the same true for MAXDOP?
    – Adrian S
    May 16 '18 at 13:15
  • 1
    No, but if you really cared you wouldn't be running on those six year old Opterons. Heh. May 16 '18 at 13:20
  • I blame the VMWare admin for that! :-) Thanks for clarify this.
    – Adrian S
    May 16 '18 at 13:33
  • You should consider Cost Threshold for parallelism and MaxDop. Best practices are : Threshold 25 or 50 (depends of queries) and MaxDop : number of logical CPU cores divide by 2. brentozar.com/archive/2013/09/…
    – liotims
    May 17 '18 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.