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