Open source · dbatools

dbatools best-practice checks: what Test-Dba really tells you

Is MAXDOP set sensibly on every instance? Does the memory limit fit? Does tempdb have enough files? For questions like these, dbatools has the Test-Dba… commands. They check one instance or a whole list in seconds and return a target value for each rule.

Tested with dbatools 2.9.1 on Nov 17, 2026 · approx. 14 min read

Part 3 · Test-DbaSQL query 1Lab SQL Server 2017–2025
Container with --cpus=2SQL Server 2017–2025 · labSQL Server sees 24 CPUs on 2 NUMA nodesnode 0node 1allowed: CPU time for 2 CPUsTest-DbaMaxDop recommendsMAXDOP 12allowed2 CPUs

For three of the most widely used checks, this page shows what they calculate, where their rules come from and what they cannot know. The rules are sound. But they work with what SQL Server reports about itself. Other instances on the same server, other services, your workload and limits that SQL Server does not see are missing from the calculation, or only partly in it.

In 60 seconds

  • Rules from well-known recommendations, inputs from SQL Server. Test-DbaMaxDop applies Microsoft's recommendation to the schedulers SQL Server creates. Test-DbaMaxMemory subtracts a reserve for the operating system from the reported memory. Test-DbaTempDbConfig checks six fixed rules.
  • On a server without special features, the calculation is right. With 24 logical CPUs on two NUMA nodes, Test-DbaMaxDop recommended MAXDOP 12 in the lab, as Microsoft's table says. Only with more than 16 CPUs per node does dbatools depart from Microsoft: it then recommends 16, Microsoft half per node.
  • What the rules do not know: other instances on the same server (MAXDOP not at all, memory only if dbatools can query the Windows services), other services such as SSIS or Reporting Services, and your workload.
  • A green result can say little. In the lab, Test-DbaMaxDop reported a database with MAXDOP 32 as Configuration is as recommended. because the instance value was right. The "File Location" rule only checks whether tempdb is on drive C:. tempdb on D: among the user databases counts as fine.
  • An empty result is not a green one. If a check cannot reach the server, it returns nothing and only writes a warning.
  • If SQL Server does not see its limits, the test recommends too much. In containers with a CPU quota, it recommended MAXDOP 12 for two permitted CPUs in the lab.

1 · Many instances, one check

The Test-Dba… commands take one or more instance names with -SqlInstance. In the lab, we checked four instances one after the other:

$cred = Get-Credential sa
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true   # lab only, this session only (part 1)
foreach ($i in 'sql2017', 'sql2019', 'sql2022', 'sql2025') {
    Test-DbaMaxDop       -SqlInstance $i -SqlCredential $cred
    Test-DbaMaxMemory    -SqlInstance $i -SqlCredential $cred
    Test-DbaTempDbConfig -SqlInstance $i -SqlCredential $cred
}

Each command returns objects that you can filter, sort and write to CSV or a table. What is calculated behind them:

CommandInputRule (2.9.1 source code)
Test-DbaMaxDopnumber of schedulers with status VISIBLE ONLINE from sys.dm_os_schedulers, NUMA nodes from sys.dm_os_memory_clerksOne node: as many as schedulers, at most 8. Several nodes from SQL Server 2016: schedulers per node, at most 16
Test-DbaMaxMemorytotal memory as SQL Server reports it (SMO PhysicalMemory); number of instances on the server via the Windows servicesReserve for the operating system following Jonathan Kehayias's method: 1 GB, plus 1 GB per 4 GB up to 16 GB and 1 GB per 8 GB above that. Below 4 GB, half. Result divided by the number of instances
Test-DbaTempDbConfigtempdb files and settings, trace flags, schedulerssix rules: TF 1118 (expected off from 2016), number of data files equal to cores up to 8, no growth in percent, not on C:\, no maximum size, data files of equal size

The rules match the recommendations we explain in TempDB setup and in the performance guide. They are a good starting point. Whether they fit your server depends on what the inputs reflect and what they do not.

2 · MAXDOP: Microsoft's table, applied to the schedulers

On an instance without resource limits, SQL Server in the lab saw all 24 logical CPUs of the host, spread across two NUMA nodes. Test-DbaMaxDop recommended 12 on all four versions, the number of CPUs per node. That matches Microsoft's recommendation for servers with several NUMA nodes and at most 16 logical CPUs per node. Because MAXDOP was still at the default of 0, the note This is the default setting. Consider using the recommended value instead. came with it.

What the rule does not take into account:

  • Other instances on the same server. The MAXDOP calculation has no instance count. Two instances on a server with 24 CPUs both get the recommendation 12. How much parallelism each instance should get is your decision, based on workload and priority.
  • Large NUMA nodes. With more than 16 logical CPUs per node, Microsoft recommends half the CPUs per node, at most 16. In that case, dbatools simply uses 16 (source code 2.9.1, lines 146–151). With two nodes of 24 CPUs each, Microsoft arrives at 12, dbatools at 16. Up to 16 CPUs per node, both agree. We compared this only in source code and documentation and did not measure it on such a server. If the instance uses soft-NUMA, Microsoft's table refers to the soft-NUMA nodes.
  • Your workload. The recommendation is an upper limit based on hardware, not an answer to how many cores a query can use to good effect. What counts when choosing the value is how quickly the important queries finish and how much CPU they need for it.
  • MAXDOP per database. From SQL Server 2016, the command also returns one row per user database with its value (DatabaseMaxDop). But it does not evaluate this value: if it is 0, the note reads Will use CurrentInstanceMaxDop value; otherwise the row takes over the instance's note. In the lab, one database was set to MAXDOP 32 and the instance to the recommended 12. On all four versions, the database row reported Configuration is as recommended. A database with its own, different value therefore does not stand out. The remedy is to look at the DatabaseMaxDop column yourself.

If you only want to evaluate the instance recommendation, filter for the row where Database is N/A, as in our lab script:

Test-DbaMaxDop -SqlInstance sql2022 -SqlCredential $cred |
    Where-Object { -not $_.Database -or $_.Database -eq 'N/A' } |
    Select-Object SqlInstance, CurrentInstanceMaxDop, RecommendedMaxDop, Notes

You set the value with Set-DbaMaxDop, first with -WhatIf.

3 · Max server memory: a reserve by formula

Test-DbaMaxMemory -SqlInstance sql2022 -SqlCredential $cred |
    Select-Object InstanceCount, Total, MaxValue, RecommendedValue

The reserve grows in steps with the memory. At 64 GB it is 11 GB: 1 GB base reserve, 4 GB for the first 16 GB and 6 GB for the 48 GB above that. In the lab, SQL Server reported 64,418 MB and the command recommended 53,154 MB. MaxValue was at the default of 2,147,483,647 everywhere.

Reserve for the operating system in GB0369121518016326496128Memory reported by SQL Server, in GBper 4 GBone more GB per 8 GB8 GB: 3 GB reserveLab: 64,418 MB reported, 11 GBreserve, 53,154 MB recommendedTest-DbaMaxMemory rule1 GB base reserve+ 1 GB per 4 GB up to 16 GB+ 1 GB per 8 GB above thatpartial steps countbelow 4 GB: halfthen divided by thenumber of instances
Reserve for the operating system in GB0369121518016326496128Memory reported by SQL Server, in GBper 4 GBone more GB per 8 GB8 GB: 3 GB reserveLab: 64,418 MBreported, 11 GBreserve, 53,154 MBrecommended1 GB base reserve, plus 1 GB per 4 GB up to 16 GBand 1 GB per 8 GB above that; partial steps count.Below 4 GB, half. Then divided by the number ofinstances.
The reserve grows in steps. The recommendation of Test-DbaMaxMemory is the reported memory minus this reserve, divided by the number of instances.

What the calculation does not know, or only partly:

  • Other services on the server. If dbatools finds Analysis Services, Reporting Services or Integration Services on the server, it warns The memory calculation may be inaccurate as the following SQL components have also been detected: …. On our Windows server with Integration Services, the warning ended in SSIS. Nothing is subtracted for these services. Virus scanners, backup agents and monitoring software are not part of the calculation at all.
  • Several instances. The recommendation is divided by the number of running instances. dbatools determines this number through the server's Windows services. If that fails, it assumes one instance and warns Couldn't get accurate SQL Server instance count on … Defaulting to 1. If dbatools itself runs on Linux or macOS, for example in a pipeline, it does not ask at all and warns on every call Can't determine instance count from Linux or Mac. Defaulting to 1. We saw this second warning in the lab. Against a Windows server with several instances, the recommendation is then too high. From Windows, the count worked: on a server with 8 GB and two instances, the command reported InstanceCount 2 and recommended 2,560 MB for each instance, that is 8 GB minus a 3 GB reserve, divided by two.
  • Small servers. Below 4 GB, the command simply recommends half of the memory.
  • SQL Server on Linux. There, SQL Server by default reports only 80 percent of the machine's memory as its own, 64,417 of 80,521 MB in the lab. dbatools subtracts the reserve from that again. The recommendation is therefore more cautious than necessary.

The recommendation is a starting value. Derive max server memory from whatever else runs on the server, and leave headroom for everything outside the buffer pool. You set the value with Set-DbaMaxMemory, first with -WhatIf.

4 · TempDB: six rules, not equally meaningful

Test-DbaTempDbConfig -SqlInstance sql2017 -SqlCredential $cred |
    Select-Object Rule, Recommended, CurrentSetting, IsBestPractice
  • File count: SQL Server 2017 had a single tempdb data file in the lab. The test asked for 8 and reported the File Count rule as violated, rightly so. SQL Server 2019, 2022 and 2025 created 8 files during setup.
  • TF 1118 Enabled: The rule expects the trace flag off from SQL Server 2016; its behavior has been the default since then.
  • File Location: The rule only checks whether a file path starts with C:. If tempdb sits on D: in the same directory as the user databases, the rule is met. It says nothing about whether tempdb has its own fast disk. On Linux, where there is no C:, it is always met. A Windows installation with default paths puts tempdb under C:\Program Files\Microsoft SQL Server\…\DATA, and the rule fires, as it did in the lab.

Why tempdb needs several files of equal size and how to set them up is covered in TempDB setup.

5 · When SQL Server does not see its limits

All three checks take over what SQL Server reports about CPUs and memory. On a physical server or a VM, that is what the instance gets: the server's CPUs or the assigned vCPUs. But there are limits that SQL Server does not always see. In the lab, we tested containers with Docker limits:

LimitVersionSQL Server seesRecommendationpermitted
CPU quota (--cpus=2)all four24 CPUsMAXDOP 12CPU time for 2 CPUs
fixed CPUs (--cpuset-cpus=0-1)2017, 201924 CPUsMAXDOP 122 CPUs
fixed CPUs2022, 20252 CPUsMAXDOP 22 CPUs
memory (--memory=4g)2017, 201964,417 MB53,154 MB4 GB
memory2022, 20253,277 MB1,639 MB4 GB
CPU · MAXDOP recommendation of Test-DbaMaxDop--cpus=2all four versionssees 24 CPUs · allowed 2 CPUs · recommended 12--cpuset-cpus=0-1SQL Server 2017, 2019sees 24 CPUs · allowed 2 CPUs · recommended 12--cpuset-cpus=0-1SQL Server 2022, 2025sees 2 CPUs · allowed 2 CPUs · recommended 2Memory · recommendation of Test-DbaMaxMemory--memory=4gSQL Server 2017, 2019sees 64,417 MB · allowed 4 GB · recommended53,154 MB--memory=4gSQL Server 2022, 2025sees 3,277 MB · allowed 4 GB · recommended 1,639 MBwhat SQL Server seesallowed limitrecommendation above the limitrecommendation fits
CPU · MAXDOP recommendation ofTest-DbaMaxDop--cpus=2all four versionssees 24 CPUs · allowed 2 CPUs · recommended 12--cpuset-cpus=0-1SQL Server 2017, 2019sees 24 CPUs · allowed 2 CPUs · recommended 12--cpuset-cpus=0-1SQL Server 2022, 2025sees 2 CPUs · allowed 2 CPUs · recommended 2Memory · recommendation ofTest-DbaMaxMemory--memory=4gSQL Server 2017, 2019sees 64,417 MB · allowed 4 GB · recommended53,154 MB--memory=4gSQL Server 2022, 2025sees 3,277 MB · allowed 4 GB · recommended1,639 MBwhat SQL Server seesallowed limitrecommendation above the limitrecommendation fits
The dbatools calculation was right every time, only the input was not: where SQL Server does not see the limit, the recommendation lies far above it. The exact values are in the table.

This matches Microsoft's documentation. SQL Server honors cgroup v2 limits only from SQL Server 2022 CU20 and 2025 onward (Microsoft Learn). Even then, SQL Server does not see a CPU quota and keeps reporting all of the host's CPUs. For that case, Microsoft advises setting processor affinity to match the quota (ALTER SERVER CONFIGURATION SET PROCESS AFFINITY) and enabling trace flag 8002. We did not try this. The 3,277 MB under the 4 GB limit are the 80 percent that SQL Server on Linux uses by default.

dbatools calculated correctly every time; only the input was wrong. A 4 GB limit with a recommendation of 52 GB is dangerous: if the buffer pool grows past the limit, the kernel kills the process. What SQL Server sees in containers and what helps is described in SQL Server in Docker: what goes wrong in the container.

Conversely, the check always tests against the current state. Setup sets the number of tempdb files since SQL Server 2016, and MAXDOP since SQL Server 2019, from the CPUs present at installation time (Microsoft: /SQLTEMPDBFILECOUNT, /SQLMAXDOP). Our Windows VM had 4 vCPUs at installation and later ran with one. SQL Server then saw a single scheduler. Test-DbaMaxDop recommended 1 instead of the configured 4 and reported Before changing MaxDop, consider that the higher value may have been intentionally set. Test-DbaTempDbConfig expected one data file instead of the four that were created. When a VM is resized, it pays to run the checks again.

An overview query shows whether an instance sees its limits. It is read-only and runs on any instance:

-- What does this instance see of CPUs and memory? (read-only)-- Compare with what the server, VM or container actually assigns-- MAXDOP 1: runs serially and does not take further cores from the applicationSELECT  (SELECT cpu_count FROM sys.dm_os_sys_info)                         AS cpu_count,  (SELECT COUNT(*) FROM sys.dm_os_schedulers     WHERE status = 'VISIBLE ONLINE')                                AS visible_schedulers,  (SELECT COUNT(DISTINCT memory_node_id) FROM sys.dm_os_memory_clerks     WHERE memory_node_id <> 64)                                     AS numa_nodes,  (SELECT physical_memory_kb / 1024 FROM sys.dm_os_sys_info)         AS physical_memory_mb,  (SELECT committed_target_kb / 1024 FROM sys.dm_os_sys_info)        AS committed_target_mb,  (SELECT CAST(value_in_use AS int) FROM sys.configurations     WHERE name = 'max degree of parallelism')                       AS maxdop,  (SELECT CAST(value_in_use AS int) FROM sys.configurations     WHERE name = 'max server memory (MB)')                          AS max_server_memory_mbOPTION (MAXDOP 1);

6 · Empty is not green

Some checks do not ask SQL Server but Windows, for example about the power plan (Test-DbaPowerPlan), the right for instant file initialization (Test-DbaInstantFileInitialization) or free space (Get-DbaDiskSpace). For that they need WMI access to the server. If that fails, they return no result, throw no error and only write a warning. According to the source code, Test-DbaPowerPlan distinguishes three cases: unsupported operating system, failed login and other errors.

We saw this in the lab against Linux servers, where there is no WMI:

CommandWarning (shortened)
Test-DbaPowerPlan`Can't get Power Plan Info for sql2022. Check logs for more details. \Windows Principal functionality is not supported on this platform.`
Test-DbaInstantFileInitialization (via Get-DbaService)`Failed to resolve or to connect to sql2022. \The term 'New-CimSessionOption' is not recognized as a name of a cmdlet, …`
Get-DbaDiskSpace`Failed to connect to sql2022. \The term 'New-CimSessionOption' is not recognized as a name of a cmdlet, …`

Windows is no different. Against a server name that could not be resolved, Test-DbaPowerPlan returned no result and wrote three warnings:

Lab output
[Resolve-DbaNetworkName] DNS name psg-gibt-es-nicht not found | No such host is known
[Get-DbaPowerPlan] Couldn't resolve hostname. Skipping.
[Test-DbaPowerPlan] Can't get Power Plan Info for psg-gibt-es-nicht. Check logs for more details. | Couldn't resolve hostname. Skipping.

Against the reachable Windows server, the same commands returned results. The power plan was set to Balanced; Test-DbaPowerPlan expects High performance and therefore reported IsBestPractice False. Microsoft itself mentions High performance in a general Windows article as a workaround when Balanced slows things down, but for varying load it leans toward Balanced. So this is a trade-off for your server, not a clear error. Instant file initialization was off for both service accounts, because setup only turns it on when asked (/SQLSVCINSTANTFILEINIT, default False). We did not reproduce WMI blocked by a firewall or missing rights; the source code follows the same pattern there.

A report that only lists hits shows nothing for unreachable servers and looks clean as a result. Include the warnings in your evaluation:

$result = Test-DbaPowerPlan -ComputerName sql2022 -WarningVariable w -WarningAction SilentlyContinue
if (-not $result -and $w) { "No result for sql2022: $($w[0])" }

Alternatively, set -EnableException; the command then stops with an error instead of only warning.

Not every warning means "no result". On a Windows server without Reporting Services, Test-DbaMaxMemory wrote Invalid namespace: root\Microsoft\SQLServer\ReportServer next to its result. That is only the search for a service that does not exist there. So check whether there is a result and read the warnings with it, rather than just counting warnings.

7 · How to read a Test-Dba… result

  1. Which input does the rule use? What SQL Server reports, what Windows reports, or a fixed assumption? If in doubt, the command's source code tells you.
  2. What does the rule not know? Other instances, other services, database settings, your workload.
  3. Does the instance see what it gets? Compare the overview query from section 5 with what the server, VM or container assigns.
  4. An empty result does not mean green. Evaluate warnings or set -EnableException.
  5. A recommendation is a starting value. Adopt it deliberately, with -WhatIf, and then check the runtime and CPU use of the important queries.

A green result means: The rule is met for what the check sees. No more and no less.

What we measured and what we did not

  • Measured (Nov 16, 2026): dbatools 2.9.1 on PowerShell 7.6.8 (Linux) against SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU9 in Linux containers on a host with 24 logical CPUs, 78 GB of memory and cgroup v2; each version without limits and with the three limits from section 5. No load.
  • Re-checked (Nov 16, 2026, DBT-VP): database with MAXDOP 32 on all four versions; overview query.
  • Measured on Windows (Nov 17, 2026): dbatools 2.9.1 under Windows PowerShell 5.1 on Windows Server 2022 (VM, 8 GB) with two instances of SQL Server 2022 RTM and Integration Services. We checked instance count, SSIS warning, tempdb rules, power plan, instant file initialization, disk space and an unresolvable server name. The VM ran with one instead of four vCPUs (section 5).
  • From the source code only: the behavior with WMI blocked or missing rights on Windows servers.
  • Not measured: the effect of the recommendations on runtime; MAXDOP with several instances on a server with several CPUs; a Windows client against remote Windows servers; Kubernetes; the other roughly 40 Test-Dba… commands.

Further reading

More parts of the series