sqmSQLTool › Commands › Get-sqmServerUtilization

Get-sqmServerUtilization Performance

Samples SQL Server utilization over multiple intervals using four DMV queries per sample: CPU (via ring buffer XML), memory (dm_os_process_memory + dm_os_sys_memory), runnable/active threads (dm_os_workers), and plan cache stats (dm_exec_query_stats). Aggregates Min/Max/Avg and outputs TXT, CSV, and an inline dark-theme HTML report.

Execution Flow

START dbatools available? throw ERROR for ($i = 1; $i -le $SampleCount; $i++) — default 6 samples × 10s = ~60s total 4 parallel Invoke-DbaQuery calls per sample ① dm_os_process_memory CROSS JOIN dm_os_sys_memory → SQLPhysicalMemoryBytes, ServerTotalMemoryBytes, AvailableMemoryBytes ② dm_os_workers → RunnableThreads (RUNNABLE), ActiveThreads (SUSPENDED + RUNNING) ③ dm_os_ring_buffers WHERE ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR' → CONVERT(XML, record).value('(//ProcessUtilization)[1]','int') → CPUUtilizationPercent ④ dm_exec_query_stats → CachedPlans (COUNT), TotalCompilations (SUM sql_handle) Add sample row to $samples[] Start-Sleep -Seconds $SampleIntervalSeconds (skip on last sample) Aggregate $samples[] Current (last sample), Min, Max, Avg for CPU / SQLMemory / AvailableMemory / RunnableThreads / ActiveThreads Build $aggregated PSCustomObject Generate-UtilizationTxtReport + Generate-UtilizationHtmlReport → TXT + CSV (raw) + HTML Return $aggregated DONE

Synopsis

Takes $SampleCount snapshots spaced $SampleIntervalSeconds apart. Each snapshot fires four Invoke-DbaQuery calls concurrently. CPU is read from the ring buffer XML (dm_os_ring_buffers) rather than the OS — this yields SQL Server's own CPU view. After all samples are collected, the function aggregates Current/Min/Max/Avg for each metric and generates three output files. The HTML report embeds inline dark-theme CSS.

Requires dbatools. Default of 6 samples × 10 s = roughly 60 s total runtime. The CSV export contains raw per-sample rows; the TXT and HTML contain the aggregated summary. Output path defaults to Get-sqmDefaultOutputPath.

Syntax

Get-sqmServerUtilization
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-SampleCount <Int>]            # default 6
    [-SampleIntervalSeconds <Int>]   # default 10
    [-OutputPath <String>]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance to sample.
-SqlCredentialPSCredential—SQL connection credential.
-SampleCountInt6Number of measurement iterations.
-SampleIntervalSecondsInt10Pause between samples (skipped after the last sample).
-OutputPathStringGet-sqmDefaultOutputPathDirectory for TXT, CSV (raw samples), and HTML report.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Return Value (Aggregated)

PropertyDescription
SqlInstanceSampled instance.
SampleCount / SampleIntervalSecondsSampling parameters used.
CPU_Current / CPU_Min / CPU_Max / CPU_AvgSQL Server CPU utilization % from ring buffer.
SQLMemoryGB_Current/Min/Max/AvgSQL Server physical memory committed in GB.
AvailableMemoryGB_Current/Min/Max/AvgAvailable OS physical memory in GB.
RunnableThreads_Current/Min/Max/AvgThreads in RUNNABLE state (CPU pressure indicator).
ActiveThreads_Current/Min/Max/AvgThreads in SUSPENDED or RUNNING state.
TxtFile / CsvFile / HtmlFileWritten report file paths.

Examples

# 60-second sample, local instance
Get-sqmServerUtilization

# Quick 3-sample snapshot with short interval
Get-sqmServerUtilization -SqlInstance "SQL01" -SampleCount 3 -SampleIntervalSeconds 5

# Show only aggregated CPU
(Get-sqmServerUtilization).CPU_Avg