sqmSQLTool › Commands › Get-sqmConnectionStats

Get-sqmConnectionStats Performance

Queries sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections to report current SQL Server connection counts grouped by Application, Login, Host, or Database. Calculates connection usage percentage against max_connections. Optionally exports CSV and an interactive HTML report.

Execution Flow

START dbatools available? throw ERROR foreach $instance in $SqlInstance [pipeline] Invoke-DbaQuery: dm_exec_sessions LEFT JOIN dm_exec_requests LEFT JOIN dm_exec_connections → $rows Read max_connections from sys.configurations Build Summary: TotalConnections / MaxConnections / UsagePct GroupBy (Application | Login | Host | Database) Group-Object by $GroupBy field → sort by Count DESC Build GroupedStats[]: GroupKey, ConnectionCount, Sessions[] Extract ActiveRequests[] (status ≠ sleeping) if $OutputPath → ShouldProcess → write CSV if $OutputHtml → ConvertTo-sqmHtmlReport + Invoke-sqmOpenReport Build instance result object Return $allResults[] DONE

Synopsis

Queries session and connection DMVs to give an instant view of who is connected to SQL Server and from where. Requires VIEW SERVER STATE. The -GroupBy parameter controls how connections are aggregated. Active requests (non-sleeping sessions) are always included as a separate list. Connection usage percentage is calculated from sys.configurations max connections.

Uses the _s localization helper for all output strings. Requires dbatools and VIEW SERVER STATE permission on the target instance.

Syntax

Get-sqmConnectionStats
    [-SqlInstance <String[]>]    # pipeline
    [-SqlCredential <PSCredential>]
    [-GroupBy <String>]           # Application|Login|Host|Database
    [-OutputPath <String>]
    [-OutputHtml]
    [-ContinueOnError]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMESQL Server instance(s) to query. Pipeline-capable.
-SqlCredentialPSCredential—Credential for the SQL connection.
-GroupByStringApplicationGrouping dimension: Application (program_name), Login (login_name), Host (host_name), or Database (database_name).
-OutputPathString—If set: write CSV to this directory.
-OutputHtmlSwitchfalseGenerate an HTML dashboard via ConvertTo-sqmHtmlReport.
-ContinueOnErrorSwitchfalseContinue with the next instance on error.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Return Value

PropertyDescription
SqlInstanceThe queried SQL Server instance.
SummaryPSCustomObject: TotalConnections, MaxConnections, ConnectionUsagePct, ActiveRequests, ActiveSessions.
GroupedStatsArray sorted by ConnectionCount DESC: GroupKey, ConnectionCount, LoginNames[], HostNames[].
ActiveRequestsSessions with non-sleeping status: SessionId, LoginName, HostName, Database, Status, WaitType, BlockedBy.
CsvFilePath to CSV file (if -OutputPath was set).
HtmlFilePath to HTML file (if -OutputHtml was set).

Examples

# Connection summary grouped by application
Get-sqmConnectionStats -SqlInstance "SQL01"

# Group by login and save CSV
Get-sqmConnectionStats -SqlInstance "SQL01" -GroupBy "Login" -OutputPath "C:\System\WinSrvLog\MSSQL"

# Show active requests only
(Get-sqmConnectionStats).ActiveRequests | Format-Table SessionId, LoginName, Status, WaitType