Max Sessions Allowed Azure SQL Database & Elastic Pools.

Date created: February 21, 2019.

A recurrent question from Azure SQL Database users is how to monitor the number of concurrent connections and sessions that count toward the tier limit.

On the DTU-based model the limit depends on the tier. For the Basic tier, the limit for maximum concurrent sessions is currently 300 sessions. Standard tier have service levels that range from 600 to 30,000 maximum concurrent sessions, while Premium tier has a fixed limit of 30,000 maximum concurrent sessions. For more information, you can read this documentation.

Meanwhile the vCore-based model has a limit of 30,000 maximum sessions. This limit appliesto all tiers: the General Purpose service tier, Business Critical service tier, and Hyperscale service tier. For more information about the vCore model you can read this documentation.

Currently a system function does not exist on Azure SQL Database to query those session limits per tier and per model.

The following query may help you monitor de number of sessions and connections:

SELECT
c.session_id, c.net_transport, c.encrypt_option,
c.auth_scheme, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.nt_domain,
s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
 

The join between sys.dm_exec_connections and sys.dm_exec_sessions on above query allows us to remove "sa" sessions that do not count over the limit of maximum sessions. Those "sa" sessions are system sessions that can be seen only for transparency purposes, so Azure customers have a better picture of internal processes for troubleshooting during incidents and to give them the possibility of mitigate issues by themselves.

In the other hand, when using elastic pools the proper way to monitor sessions is to query a system view named sys.elastic_pool_resource_stats.

SELECT max_session_percent
FROM sys.elastic_pool_resource_stats
ORDER BY end_time DESC

By the way, the maximum session limit for elastic pools is 30,000 and there is no way to query this limit using T-SQL.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.