[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Max Sessions Allowed Azure SQL Database & Elastic Pools

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

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.