Commands / Get-sqmPerfCounters
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.
-NoOpenswitch$falseDo not automatically open the generated report after creation.

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

Example 1, All counters, local instance

Get-sqmPerfCounters

Example 2, Filter to Buffer and Memory categories

Get-sqmPerfCounters -SqlInstance "SQL01" -Category "Buffer","Memory"

Example 3, Show only flagged counters

(Get-sqmPerfCounters).Counters | Where-Object { $_.Interpretation -ne '' }
⬆ Index
All Commands