CODEWITHSUNDEEP
c#sqlsql-serverazureazure-sql-database
No1Lives4Ever
No1Lives4Ever

Reputation: 6893

Running connections on Azure-Sql database to "Master"

I using Azure-Sql-Server. Hosting my database there and using Entity-Framework (C#) on top of it. I using Standart S3 tier. This pricing tier is limited to 200 concurrent connection to DB.

My system working in Micro-services architecture. Each of my services are have open connections to the DB.

This is how is my connection string looks like

metadata=res:///Model1.csdl|res:///Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=mydb.database.windows.net;Initial Catalog=production;Persist Security Info=True;User ID=user@my;Password=mypass;Pooling=true;Max Pool Size=2;Enlist=false;"

from time to time I monitoring the number of connection to my DB.

By using 

SELECT DB_NAME(eS.database_id) AS the_database, eS.is_user_process, COUNT(eS.session_id) AS total_database_connections
    FROM sys.dm_exec_sessions eS 
    GROUP BY DB_NAME(eS.database_id)
        , eS.is_user_process
    ORDER BY 1, 2;
    END

which gives me the result:

enter image description here

If I'm sum all the connections I get the result of total 215 connection. This might me align with some connection limits that I'm find from time to time.

  1. The question is what are all of those connections "non-user-process" to "master"? (111 connections) who ask them? who need them? how to find?
  2. Is all those connections from above are being counter as part of the 200 connections limit?

Thanks!

Upvotes: 0

Views: 219

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15698

Those sessions that you are seeing as is_user_process = 0 are sessions started by Azure monitoring and alerting, they are not counted as part of the tier limit for sessions and they can be safely ignored . Later on 2017 the Database Engine team removed the filter that was hiding back-end sessions/connections of these types. The reason this lockdown was removed was so that you could have increased visibility into the sessions that are connecting to your database. For more information, please read here Devin Rider (MSFT) contributions on a forum thread.

Please monitor the number of sessions only for your user databases and do not take in consideration those back-end sessions.

SELECT *
FROM sys.dm_exec_sessions s, sys.databases d
WHERE s.database_id =d.database_id
and d.name='youruserdatabase'

Upvotes: 2

Related Questions