sqmSQLTool › Commands › Get-sqmPerfCounters

Get-sqmPerfCounters Performance

Reads key SQL Server performance counters from sys.dm_os_performance_counters: Buffer Cache Hit Ratio, Page Life Expectancy, Batch Requests/sec, compilations, lock waits, deadlocks, memory grants, blocking, and more. Automatically interprets notable values. Optionally saves CSV and HTML report.

Execution Flow

START dbatools available? throw ERROR Invoke-DbaQuery: sys.dm_os_performance_counters TOP $TopN Buffer cache hit ratio (cntr_type=537003264 ratio pair) Page life expectancy, Batch Requests/sec, SQL Compilations/sec Lock Waits/sec, Deadlocks/sec, User Connections, Memory (Server/Target/Stolen) Full Scans, Lazy Writes, Memory Grants Pending, Processes Blocked, Plan Cache Active Temp Tables, Temp Tables Creation Rate, Free list stalls/sec Compute BHR% from ratio pair; extract PLE, UserConnections Apply -Category filter if specified foreach row → _s localized interpretation PLE < 300 → Critical; < 600 → Warning Memory Grants Pending, Processes Blocked, Deadlocks, Lazy Writes, Re-Compilations if $OutputPath → CSV + ConvertTo-sqmHtmlReport → HTML Return {Summary, Counters[]} DONE

Synopsis

A single DMV query against sys.dm_os_performance_counters returns the ~20 most important SQL Server health indicators. Buffer Cache Hit Ratio is computed from the ratio counter pair. Page Life Expectancy, blocking, deadlocks, memory pressure, and re-compilations are automatically flagged with localized interpretation strings via the _s helper. Results are returned as a structured object with a Summary and a Counters array.

Requires dbatools and VIEW SERVER STATE. Uses the _s localization helper for all interpretation strings. Counters values are point-in-time snapshots, not rates — for /sec counters, two readings are needed to calculate the actual rate.

Syntax

Get-sqmPerfCounters
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-Category <String[]>]         # e.g. @('Buffer','Memory','Locks')
    [-TopN <Int>]                   # 1-500, default 50
    [-OutputPath <String>]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance to query.
-SqlCredentialPSCredential—Credential for the SQL connection.
-CategoryString[]allFilter counters by ObjectName fragment. Example: @('Buffer','Locks').
-TopNInt50Maximum number of counter rows (1–500).
-OutputPathString—If set: write CSV and HTML to this directory.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Return Value

PropertyDescription
Summary.BufferCacheHitRatioPctComputed BHR percentage from ratio counter pair.
Summary.PageLifeExpectancyPLE value in seconds.
Summary.UserConnectionsCurrent user connection count.
Summary.WarningsNumber of counters with non-empty Interpretation.
Counters[]Array of PSCustomObject: Category, CounterName, InstanceName, Value, Interpretation.

Examples

# All counters, local instance
Get-sqmPerfCounters

# Filter to Buffer and Memory categories
Get-sqmPerfCounters -SqlInstance "SQL01" -Category "Buffer","Memory"

# Show only flagged counters
(Get-sqmPerfCounters).Counters | Where-Object { $_.Interpretation -ne '' }