Commands / Test-sqmTempDbFileCount
Diagnostics & Health

Test-sqmTempDbFileCount

Counts TempDB data files (RowData only, log excluded) via SMO and compares against Microsoft's recommendation of one file per logical core, capped at a configurable maximum (default 8). Too few TempDB files is a classic cause of PAGELATCH contention on allocation pages.

Module: sqmSQLTool
Requires: SMO
ShouldProcess: No
Output: PSCustomObject

Execution Flow

START MaxFiles: explicit param, else Get-sqmConfig 'CheckTempDbMaxFiles', else 8 SMO: Server.Processors.Count (fallback: WMI Win32_Processor) RecommendedCount = min(LogicalCores, MaxFiles) tempdb.FileGroups.Files → count RowData files only FileCount == RecommendedCount? Status = OK Status = Warning Return PSCustomObject (CurrentFileCount, RecommendedCount, LogicalCores, Status) DONE
Only data files count toward the comparison — the single TempDB log file is intentionally excluded, since splitting the log provides no benefit (unlike data files, which parallelize allocation across proportional-fill round-robin). Read-only diagnostic; use Set-sqmServerSetting, T-SQL ALTER DATABASE tempdb ADD FILE, or dbatools to add files.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalSQL Server instance. Default: local computer name.
-SqlCredentialPSCredentialOptionalSQL authentication credential for the connection.
-MaxFilesInt (1–64)OptionalMaximum recommended file count, capping the CPU-based recommendation. Default: 8, or the module config key CheckTempDbMaxFiles if set and this parameter wasn't given explicitly.
-EnableExceptionSwitchOptionalThrow a terminating exception instead of Write-Error.

Return Value

Returns a single PSCustomObject with: SqlInstance, CurrentFileCount, RecommendedCount, LogicalCores, Status (OK, Warning, or Error), Message.

Examples

Example 1 — Check TempDB file count

Test-sqmTempDbFileCount -SqlInstance "SQL01"

Example 2 — Cap the recommendation at 4 files

Test-sqmTempDbFileCount -SqlInstance "SQL01\INST1" -MaxFiles 4
⬆ Index
All Commands