Sessions Percentage

Microsoft.SqlServer.Azure.UnitMonitor.ElasticPoolMaxSessionUtil (UnitMonitor)

Monitors the Sessions Utilization consumed by all databases in Elastic Pool.
Note that 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 total Sessions utilization of this Elastic Database Pool.

Note that 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 total amount of sessions opened approaching or exceeding the limit configured for the monitor.

Resolutions

Determine why so much sessions are being opened, and take any corrective actions if necessary.
Scale up Elastic Database Pool tier to higher level
Distribute maintenance workflows for databases over time.
Reduce amount of databases added to this Elastic Database Pool.
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.	90
Enabled	Enables or disables the workflow.	Yes
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$
Request timeout (seconds)	Gets or sets the timespan to wait before the request times out.	60
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.	80

Element properties:

Target

Microsoft.SqlServer.Azure.ElasticPool

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

MatchMonitorHealth

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SqlServer.Azure.UnitMonitorType.ElasticPoolResourceStats

Remotable

True

Accessibility

Public

Alert Message

Sessions utilization exceeds the configured limit

The total sessions utilization for Elastic Pool "{0}" on server "{1}" exceeds the configured limit by {2}\%.

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SqlServer.Azure.UnitMonitor.ElasticPoolMaxSessionUtil" TypeID="Microsoft.SqlServer.Azure.UnitMonitorType.ElasticPoolResourceStats" Target="Microsoft.SqlServer.Azure.ElasticPool" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" Accessibility="Public" Enabled="true">

  <Category>PerformanceHealth</Category>

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

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

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

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

      <AlertParameter3>$Data/Context/Property[@Name='Value']$</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/Host/Property[Type="Microsoft.SqlServer.Azure.Server"]/ServerName$</ServerName>

    <ElasticPoolName>$Target/Property[Type="Microsoft.SqlServer.Azure.ElasticPool"]/ElasticPoolName$</ElasticPoolName>

    <PricingTier>$Target/Property[Type="Microsoft.SqlServer.Azure.ElasticPool"]/PricingTier$</PricingTier>

    <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>

    <HttpTimeoutSeconds>60</HttpTimeoutSeconds>

    <ResourceName>$Target/Property[Type="Microsoft.SqlServer.Azure.ElasticPool"]/ElasticPoolName$</ResourceName>

    <MetricName>sessions_percent</MetricName>

    <WarningThreshold>80</WarningThreshold>

    <CriticalThreshold>90</CriticalThreshold>

  </Configuration>

</UnitMonitor>