Sessions Count

Microsoft.SqlServer.Azure.UnitMonitor.DatabaseNumberOfSessions (UnitMonitor)

Monitors the number of database sessions.
Note that this monitor works through T-SQL only.
In order to prevent incorrect data collection, the value of Interval (seconds) overridable parameter should not be set lower than the default value.

Knowledge Base article:

Summary

Monitors the number of sessions created by the clients connecting to the database.

Note that this monitor works through T-SQL only.

In order to prevent incorrect data collection, the value of Interval (seconds) overridable parameter should not be set lower than the default value.

Causes

Unhealthy state is caused by the number of clients connecting to the database approaching or exceeding the limit configured for the monitor.

Resolutions

Determine why so many client connections are being created, and take any corrective actions if necessary.
Move data from this database to multiple databases using a sharding algorithm, which avoids large numbers of connections.
If sharding is already being used, review any other sharding algorithms to help avoid databases with large numbers of connections.
Adjust the configured limits by means of an override.
Disable the monitor.

Overrideable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	MatchMonitorHealth
Critical Threshold	The monitor will change the state to 'Critical' if the value reaches this threshold.	250
Enabled	Enables or disables the workflow.	No
Generates Alerts	Defines whether the workflow generates an Alert.	Yes
Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.	900
Monitoring Mode	Defines monitoring mode.	$Target/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/MonitoringMode$
Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	600
Timeout for database connection (seconds)	A period in which the workflow must establish connection to the database.	30
Warning Threshold	The monitor will change the state to 'Warning' if the value reaches this threshold.	200

Element properties:

Target

Microsoft.SqlServer.Azure.UserDatabase

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

False

Alert Generate

True

Alert Severity

MatchMonitorHealth

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SqlServer.Azure.UnitMonitorType.DatabaseSessions

Remotable

True

Accessibility

Public

Alert Message

Number of sessions exceeds the configured limit

The number of sessions for database "{0}" on server "{1}" exceeds the configured limit by {2} sessions.

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SqlServer.Azure.UnitMonitor.DatabaseNumberOfSessions" TypeID="Microsoft.SqlServer.Azure.UnitMonitorType.DatabaseSessions" Target="Microsoft.SqlServer.Azure.UserDatabase" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" Accessibility="Public" Enabled="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SqlServer.Azure.UnitMonitor.DatabaseNumberOfSessions.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="Microsoft.SqlServer.Azure.Database"]/DatabaseName$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="Microsoft.SqlServer.Azure.Database"]/ServerName$</AlertParameter2>

      <AlertParameter3>$Data/Context/Property[@Name='SessionsCount']$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Healthy" MonitorTypeStateID="Healthy" HealthState="Success"/>

    <OperationalState ID="Warning" MonitorTypeStateID="Warning" HealthState="Warning"/>

    <OperationalState ID="Critical" MonitorTypeStateID="Critical" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>600</TimeoutSeconds>

    <ServerName>$Target/Property[Type="Microsoft.SqlServer.Azure.Database"]/ServerName$</ServerName>

    <DatabaseName>$Target/Property[Type="Microsoft.SqlServer.Azure.Database"]/DatabaseName$</DatabaseName>

    <UseRest>$Target/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/UseREST$</UseRest>

    <MonitoringMode>$Target/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/MonitoringMode$</MonitoringMode>

    <AuthorityDetails>$Target/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/AuthorityDetails$</AuthorityDetails>

    <SubscriptionId>$Target/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/Subscription$</SubscriptionId>

    <ResourceGroupName>$Target/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/ResourceGroupName$</ResourceGroupName>

    <SqlTimeoutSeconds>30</SqlTimeoutSeconds>

    <PropertyName>SessionsCount</PropertyName>

    <WarningThreshold>200</WarningThreshold>

    <CriticalThreshold>250</CriticalThreshold>

  </Configuration>

</UnitMonitor>