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.
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-DbaMaxDopapplies Microsoft's recommendation to the schedulers SQL Server creates.Test-DbaMaxMemorysubtracts a reserve for the operating system from the reported memory.Test-DbaTempDbConfigchecks six fixed rules. - On a server without special features, the calculation is right. With 24 logical CPUs on two NUMA nodes,
Test-DbaMaxDoprecommended 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-DbaMaxDopreported a database with MAXDOP 32 asConfiguration is as recommended.because the instance value was right. The "File Location" rule only checks whether tempdb is on driveC:. tempdb onD: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:
| Command | Input | Rule (2.9.1 source code) |
|---|---|---|
Test-DbaMaxDop | number of schedulers with status VISIBLE ONLINE from sys.dm_os_schedulers, NUMA nodes from sys.dm_os_memory_clerks | One node: as many as schedulers, at most 8. Several nodes from SQL Server 2016: schedulers per node, at most 16 |
Test-DbaMaxMemory | total memory as SQL Server reports it (SMO PhysicalMemory); number of instances on the server via the Windows services | Reserve 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-DbaTempDbConfig | tempdb files and settings, trace flags, schedulers | six 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 readsWill 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 reportedConfiguration is as recommended.A database with its own, different value therefore does not stand out. The remedy is to look at theDatabaseMaxDopcolumn 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, NotesYou 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, RecommendedValueThe 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.
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 inSSIS. 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 callCan'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 reportedInstanceCount2 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 Countrule 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 withC:. If tempdb sits onD: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 noC:, it is always met. A Windows installation with default paths puts tempdb underC:\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:
| Limit | Version | SQL Server sees | Recommendation | permitted |
|---|---|---|---|---|
CPU quota (--cpus=2) | all four | 24 CPUs | MAXDOP 12 | CPU time for 2 CPUs |
fixed CPUs (--cpuset-cpus=0-1) | 2017, 2019 | 24 CPUs | MAXDOP 12 | 2 CPUs |
| fixed CPUs | 2022, 2025 | 2 CPUs | MAXDOP 2 | 2 CPUs |
memory (--memory=4g) | 2017, 2019 | 64,417 MB | 53,154 MB | 4 GB |
| memory | 2022, 2025 | 3,277 MB | 1,639 MB | 4 GB |
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:
| Command | Warning (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:
[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
- 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.
- What does the rule not know? Other instances, other services, database settings, your workload.
- Does the instance see what it gets? Compare the overview query from section 5 with what the server, VM or container assigns.
- An empty result does not mean green. Evaluate warnings or set
-EnableException. - 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
- dbatools: Test-DbaMaxDop, Test-DbaMaxMemory, Test-DbaTempDbConfig.
- Microsoft Learn: Configure the max degree of parallelism, Server memory configuration options, tempdb (file count at setup, trace flag 1118), mssql-conf (80 percent limit on Linux), Configure containers (cgroup v2).
- Jonathan Kehayias: How much memory does my SQL Server actually need? (origin of the memory reserve).
- At PSG: TempDB setup, SQL Server performance, SQL Server in Docker: what goes wrong in the container, dbatools overview.