sqmSQLTool v1.4

Database Health
& Best Practices

Automated health checks - from MaxMemory to orphaned files

health functions

<5min

full check

100%

PowerShell-native

Database Health Summary - SRV-SQL01

MaxMemory : OK (12 GB / 16 GB available)

MaxDop : WARN (0 instead of 4 recommended)

TempDB : OK (4 files, equal size)

Statistics : OK (last updated: today)

Problem

SQL Server defaults are rarely optimal

MaxMemory = 2147483647 MB
SQL Server claims unlimited RAM - the OS starves

MaxDop = 0
Uncontrolled parallelism on multi-core servers

TempDB = 1 file
Contention under high load

Statistics out of date
Suboptimal execution plans, slow queries

Logs grown out of control
LDF files fill up drives

Orphaned MDF/NDF files
Wasted storage, a security risk

sqmSQLTool checks all of these automatically - across any number of instances at once

Overview

10 functions in 4 categories

⚙ Instance configuration

Test-sqmMaxMemory
Test-sqmMaxDop
Test-sqmCostThreshold
Test-sqmTempDbFileCount
Get-sqmTempDbRecommendation

📊 Database maintenance

Invoke-sqmUpdateStatistics
Invoke-sqmLogShrink

🔍 File analysis

Get-sqmOrphanedFiles
Find-sqmDatabaseObject

📋 Overall report

Get-sqmDatabaseHealth (HTML/CSV/TXT)

Health report

Get-sqmDatabaseHealth

# Single instance
Get-sqmDatabaseHealth -ComputerName "SRV-SQL01"

# Entire fleet via AD OU
Get-sqmDatabaseHealth -Remote \`
  -SearchOU "OU=DB,DC=corp,DC=de"

# Open the HTML report
Get-sqmDatabaseHealth -ComputerName "SRV-SQL01" \`
  -OutputFormat HTML

✓ Opens the browser automatically | Color-coded OK / WARN / ERROR

Database Health Report - SRV-SQL01

█ Configuration

MaxMemory : OK (12 GB)

MaxDop : WARN (0)

CostThreshold : OK (50)

█ TempDB

Files : OK (4)

█ Maintenance

Statistics : OK (current)

Log size : WARN (SalesDB 8 GB)

Configuration

Setting MaxMemory & MaxDop correctly

Test-sqmMaxMemory

Test-sqmMaxMemory -ComputerName "SRV-SQL01"

RecommendedMB : 12288
CurrentMB     : 2147483647
Status        : WARN
Message       : No limit set

Recommendation: total RAM minus 2 GB for the OS, calculated per NUMA node

Test-sqmMaxDop

Test-sqmMaxDop -ComputerName "SRV-SQL01"

RecommendedDop : 4
CurrentDop     : 0
NUMANodes      : 2
LogicalCores   : 16
Status         : WARN

Calculated per Microsoft best practice (cores / NUMA, max. 8)

TempDB

Optimizing TempDB configuration

# Check file count vs. CPU cores
Test-sqmTempDbFileCount -ComputerName "SRV-SQL01"

# Generate a recommendation + script
Get-sqmTempDbRecommendation -ComputerName "SRV-SQL01"

Rule of thumb: 1 file per logical core, max. 8. All files the same size.

TempDB Recommendation

LogicalCores : 8

CurrentFiles : 1

RecommendedFiles : 8

CurrentSizeMB : 8

RecommendedSizeMB: 1024

AutogrowthEqual : True

ALTER DATABASE tempdb ADD FILE ...

Configuration

Test-sqmCostThreshold

Test-sqmCostThreshold -ComputerName "SRV-SQL01"

CurrentValue     : 5
RecommendedValue : 50
Status           : WARN
Message          : Value too low

Default is 5 - unchanged since SQL Server 7.0. Recommended: 50.

Why does it matter?

Cost Threshold controls the estimated plan cost above which SQL Server switches to parallel execution.

Value 5: almost every non-trivial query goes parallel → high CPU load

Value 50: only genuinely expensive queries use parallelism

Maintenance

Statistics & the transaction log

Invoke-sqmUpdateStatistics

# All databases on an instance
Invoke-sqmUpdateStatistics -ComputerName "SRV-SQL01"

# Specific databases only
Invoke-sqmUpdateStatistics -ComputerName "SRV-SQL01" \`
  -DatabaseList "AdventureWorks","Sales"

UPDATE STATISTICS WITH FULLSCAN - logs the duration per database

Invoke-sqmLogShrink

# WhatIf - shows what would be shrunk
Invoke-sqmLogShrink -ComputerName "SRV-SQL01" -WhatIf

# With a minimum size
Invoke-sqmLogShrink -ComputerName "SRV-SQL01" \`
  -MinShrinkMB 500

Only shrinks in Full recovery model, and only free log space

File analysis

Finding orphaned files & objects

Get-sqmOrphanedFiles

Get-sqmOrphanedFiles -ComputerName "SRV-SQL01"

FilePath  : D:\MSSQL\DATA\OldDB.mdf
SizeGB    : 24.5
LastWrite : 2025-03-15
Status    : Orphaned

Never delete without checking first - always run WhatIf first

Find-sqmDatabaseObject

Find-sqmDatabaseObject -ComputerName "SRV-SQL01" \`
  -ObjectName "sp_GetCustomer"

# With a type filter
Find-sqmDatabaseObject -ComputerName "SRV-SQL01" \`
  -ObjectName "tbl_Orders%" \`
  -ObjectType "TABLE"

Scale

Fleet-wide health checks

# Entire fleet via AD OU
Get-sqmDatabaseHealth -Remote \`
  -SearchOU "OU=SQLServer,DC=corp,DC=de" -OutputFormat HTML

# CSV export for every server
Get-Content "server-list.txt" | ForEach-Object {
  Get-sqmDatabaseHealth -ComputerName $_ -OutputFormat CSV
} | Export-Csv "health-report.csv"

🔄 Parallel

Parallel CIM sessions - 50 servers in under 2 minutes

📄 CSV export

Every check as a flat table for Excel or a SIEM

📊 HTML dashboard

Color-coded report, opens straight in the browser

Reference

Quick reference

Function	Category	Description	Remote
Get-sqmDatabaseHealth	Report	Overall report of all health checks	Yes
Test-sqmMaxMemory	Config	MaxMemory recommendation vs. current	Yes
Test-sqmMaxDop	Config	MaxDop recommendation per best practice	Yes
Test-sqmCostThreshold	Config	Check Cost Threshold for Parallelism	Yes
Test-sqmTempDbFileCount	TempDB	File count vs. CPU cores	Yes
Get-sqmTempDbRecommendation	TempDB	Generate a recommendation + script	Yes
Invoke-sqmUpdateStatistics	Maintenance	Update statistics (FULLSCAN)	Yes
Invoke-sqmLogShrink	Maintenance	Safely shrink transaction logs	Yes
Get-sqmOrphanedFiles	Files	Track down orphaned DB files	Yes
Find-sqmDatabaseObject	Search	Find objects across every database	Yes

Summary

Database health at the push of a button

automated health checks