· Uwe Janke
Commands / Get-sqmTempDbRecommendation

Get-sqmTempDbRecommendation

Configuration sqmSQLTool v1.8.2+ · Best Practices
Analyzes the current TempDB configuration and provides optimization recommendations.

Checks:
• File count: compares against CPU core count (ideal: min of CPU cores and 8)
• File sizes: alerts if unequal
• Autogrow: recommends MB-based growth instead of percent
• Drive placement: recommends separate drives if all files are on the same drive

Returns status (OK / Warning / Info) and list of recommendations.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional, Credentials for the connection.
-OutputPathstringOptional, Optional CSV export path.
-EnableExceptionswitchOptional$falseRe-throw exceptions instead of returning $null.
-NoOpenswitchSwitch$falseDo not open the HTML report after creation.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES Connect-DbaInstance → read tempdb file group Per file: size (MB), autogrow value + type, physical path CPU core count from the connected server object RecommendedCount = min(CPU cores, 8) Four independent checks, each can add a Warning message: 1) FileCount ≠ RecommendedCount 2) File sizes not all equal 3) Autogrow uses Percent instead of a fixed MB step 4) Autogrow step > 1024 MB Any hit → Status = Warning (messages accumulate, checks don't short-circuit) All files on the same drive? YES Add message; Status → Info if still OK NO Build [PSCustomObject] result -OutputPath given? NO YES Export-Csv → TempDbRecommendation_*.csv ConvertTo-sqmHtmlReport → *.html Arrays flattened to comma-joined text for the CSV/HTML columns Open HTML report unless -NoOpen Return $result Any error above → log ERROR, return $null, or throw with -EnableException DONE

Output

Returns a [PSCustomObject] with:
• SqlInstance, target instance
• Status, OK, Warning, or Info
• FileCount, current number of TempDB files
• RecommendedCount, ideal file count
• FileSizesMB, array of current file sizes
• GrowthMB, array of autogrow values
• Paths, array of file paths
• Recommendations, combined message string

Examples

Check local instance TempDB configuration
Get-sqmTempDbRecommendation -SqlInstance "SQL01"
Check TempDB and export results to CSV
Get-sqmTempDbRecommendation -SqlInstance "SQL01" -OutputPath "D:\Reports\tempdb-rec.csv"
← Back
Command Index
Live Example
View Report