none
Which connections count toward the Max Concurrent Workers limitation? RRS feed

  • Question

  • Is there a query to get the connections that count toward the server Max concurrent workers per pool limit? I'd like to be able to run something like sys.dm_exec_session/connections/sp_who2 and see how they are allocated.

    If i run sp_who2 on the instance, i can see 400+ connections BUT some (130) are from sa (mostly on master), some are on our DB but seem to be azure BackupService, DMVCollector etc. How can i tell if a connection counts toward that MaxConcWorker limitation (ours is currently 420)?

    Friday, February 8, 2019 2:42 PM

All replies

  • Hello,

    The following query should give the connections that count and the sessions related to those conenctions.

    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 following statement shows you the maximum number of connections for the current tier.

    SELECT @@MAX_CONNECTIONS AS 'Max Connections';

    If you want to this from .NET code you can use the performance counter class.

    https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.performancecounter?view=netframework-4.7.2


    Hope this helps.


    Regards,



    Alberto Morillo
    SQLCoffee.com


    Friday, February 8, 2019 7:27 PM
  • Hey Alberto,

    The join to sessions seems to be filtering out those SA connections...does that basically mean that anything that has a session entry basically counts toward the limit (ie, the DMVCollector would then count)?

    For @@Max_Connections, we see 32767 but we are in an elastic pool and vcore instance so we get an error once we hit 420. Should @@Max_Connections be reporting 420 in our case? That number seems the overall server limit. Or, are you saying thats the max we could hope for if we scaled our tier all the way up?

    Thx!

    Shawn

    Friday, February 8, 2019 9:09 PM
  • Hello,

    You need to filter those sa connections, please read the reason why from a Microsoft engineer om the following thread:

    https://social.msdn.microsoft.com/Forums/azure/en-US/977310fe-933c-4a58-9b0e-6c1dd2648e9d/large-number-of-sessions-on-a-new-database?forum=ssdsgetstarted


    About the @@Max_Connections please allow me to investigate. Please allow me some time to come back to you.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    Sunday, February 10, 2019 6:31 PM
  • The join to sessions seems to be filtering out those SA connections...does that basically mean that anything that has a session entry basically counts toward the limit (ie, the DMVCollector would then count)?


    I believe only active connections count towards the max limit. Add "WHERE status = 'running'" to Alberto's query.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, February 10, 2019 7:07 PM
  • Hello,

    Thank you for your patience Shawn. Thank you for your help Dan.

    The information I received is to look at the max_session_percent column on the sys.elastic_pool_resource_stats system catalog view to know the maximum concurrent sessions in percentage based on the limit of the pool. The max_worker_percent column is another column on that view that you may be interested on.


    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-elastic-pool-resource-stats-azure-sql-database?view=azuresqldb-current

    Maximum number of sessions is 30k for all elastic pools.  It seems there is no way to query this limit using T-SQL.

    Hope this helps. Thank you again for your patience.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, February 11, 2019 6:24 PM