locked
Difference between Max concurrent sessions vs Max concurrent workers (requests) RRS feed

  • Question

  • Dear All,

    We have developed a Micro service using Service Fabric (using the stateless Web API model). We are using SQL Azure for persistence. Right now we are doing a performance test for 3000 concurrent users. Right now, our pricing tier is S0. Please refer https://docs.microsoft.com/en-us/azure/sql-database/sql-database-resource-limits. During the test, we are getting an exception like below

    System.Data.Entity.Core.EntityException: An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy. ---> System.Data.Entity.Core.EntityException: An error occurred while starting a transaction on the provider connection. See the inner exception for details. ---> System.Data.Entity.Core.EntityException: An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy. ---> System.Data.SqlClient.SqlException: Resource ID : 1. The request limit for the database is 60 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.

    I just wanted to know the pricing tier needed to support such a load(3000 concurrent users). Let me describe about my data access layer. We are using EF 6.0 and EF DataContext is created per HttpRequest(set with below statement using structure map)

    For<MyDataContext>().Use(x => new MyDataContext()).Transient();

    The DB access is performed using the same SQL Server User(read from app.config which is set under connectionstrings). I also wanted to know the difference between Max concurrent sessions and Max concurrent workers. The exceptions says that my max. request quota exceeded(which is 60 as of now). Any kind of advise is helpful to solve my issue.

     

    Monday, June 5, 2017 8:57 AM

Answers

  • I don't know full architecture of your app but if you're using single SQL Server user, then you're probably using sessions and not logins.

    In that case, for 3000 concurrent user, you'll need any Premium Service tier Azure SQL. Premium tier supports 30.000 concurrent sessions and should be enough. Best standard plan is S3 and it supports only 2400 councurrent sessions, so it will not support your needs.

    In case that you're using logins and not sessions, only plan that can help you is P15 that supports up to 6400 concurrent logins. Once again, based on what you wrote, chance is that this isn't your case.

    And as far as concurrent sessions/workers thing, here how it works.

    Lets say you have 10 developers working on your app and they all connect to Azure SQL using SSMS. They will create 10 concurrent logins/workers and 10 sessions in Azure SQL.

    Now let's say that your app is using one login for connecting to Azure SQL and you have 100 concurent users on your app. This will create one concurent login/worker and 100 concurrent sessions in Azure SQL.


    Mustafa Toroman, Azure MVP

    • Proposed as answer by rnihad Tuesday, July 18, 2017 2:53 PM
    • Marked as answer by pituachMVP Sunday, January 6, 2019 12:20 AM
    Monday, June 5, 2017 10:25 AM

All replies

  • I don't know full architecture of your app but if you're using single SQL Server user, then you're probably using sessions and not logins.

    In that case, for 3000 concurrent user, you'll need any Premium Service tier Azure SQL. Premium tier supports 30.000 concurrent sessions and should be enough. Best standard plan is S3 and it supports only 2400 councurrent sessions, so it will not support your needs.

    In case that you're using logins and not sessions, only plan that can help you is P15 that supports up to 6400 concurrent logins. Once again, based on what you wrote, chance is that this isn't your case.

    And as far as concurrent sessions/workers thing, here how it works.

    Lets say you have 10 developers working on your app and they all connect to Azure SQL using SSMS. They will create 10 concurrent logins/workers and 10 sessions in Azure SQL.

    Now let's say that your app is using one login for connecting to Azure SQL and you have 100 concurent users on your app. This will create one concurent login/worker and 100 concurrent sessions in Azure SQL.


    Mustafa Toroman, Azure MVP

    • Proposed as answer by rnihad Tuesday, July 18, 2017 2:53 PM
    • Marked as answer by pituachMVP Sunday, January 6, 2019 12:20 AM
    Monday, June 5, 2017 10:25 AM
  • Lets say you have 10 developers working on your app and they all connect to Azure SQL using SSMS. They will create 10 concurrent logins/workers and 10 sessions in Azure SQL.

    As Mustafa said, although typically when using SSMS you have one session just for the object explorer and then additional sessions (connections) for each query window you have open, so it would probably look more like 10 logins and 20+ sessions (spids).

    Then one session (spid) may consume multiple workers, especially if you're on an Azure level with multiple cores and parallelism for larger queries.

    See the link below, any premium level below P15 is going to have fewer than 3000 max concurrent workers.  SQL Server doesn't handle it too well when you run out of workers and Azure SQL may have even more problems when that occurs.

    So, how do you do your microservices on smaller Azure configurations?  Not directly.  SQL Azure isn't really built for that.  You probably have to add some form of queuing somewhere in your architecture, you have to throttle your own workload so you don't overwhelm or confuse Azure.

    Josh

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-resource-limits


    • Edited by JRStern Monday, June 5, 2017 6:36 PM
    Monday, June 5, 2017 6:34 PM
  • Hello Mustafa,

    Thanks a lot for your reply. My design is simple(like an on-premise application) where one sql server user name and password is kept in app.config. The same credentials are used by EF DB context. So if I understand correctly, maximum number of concurrent sessions is going to play a crucial role in my case. So I had a look into the service tier https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers. According to this I believe to support 3000 concurrent users I may need P1(which supports 30000). Please correct me if I am wrong.

    I have one more additional question. Does this max concurrent sessions is per user or in total. What I am asking is suppose if I can create one more SQL user am I going to get another 30000 concurrent sessions. If so I can think about creating SQL users per micro service(SF application).

    Tuesday, June 6, 2017 5:55 AM
  • Mustafa, what would happen if I have an app running on 1000 devices at the same time. This app is connecting to Azure SQL DB through Azure App Service and the Azure App Service uses the same Azure DB Admin credentials to connect to database. Would this be counted as one login/concurrent worker and 1000 concurrent sessions? or Would this be counted as 1000 logins/concurrent workers and 1000 concurrent sessions? 
    Thursday, July 19, 2018 12:31 PM
  • Hello

    sessions are the number of active session on the server (a session can be in "sleeping"  or other status )

    requests are the running or runnable or suspended sessions on the server,  (referring to is_user_process sessions)

    so you can have 200 sessions logged, and 180 are sleeping (in a moment) and in the same moment 20 are in running or runnable or suspended status. so you would have 200 sessions, 20 workers (requests)

    to be precise sys.dm_exec_requests returns only runnable, running, suspended status for "is_user_process" sessions (user sessions where spid >50) , for system session (spid <= 50) "is_user_process" is 0 and the status can be runnable, running, suspended, background, sleeping.


    • Edited by baleng Tuesday, September 11, 2018 3:18 PM
    Tuesday, September 11, 2018 2:39 PM