locked
Logins versus sessions RRS feed

  • Question

  • I'm not sure I understand this correctly.

    Say for a P4 level there is a limit of 800 concurrent logins but you can have 9,600 sessions.

    What does that mean?

    Does it mean I can have 800 different people log in, and each can have twelve sessions, like twelve windows in an SSMS user, or twelve connections in a connection pool?

    I guess the question comes down to, is a connection a login or a session?

    Can I have one login that has 9,600 sessions, with a big, fat connection pool?

    Thanks,

    Josh

    Saturday, November 26, 2016 6:27 AM

Answers

  • Let's say that you have 10 people (DBAs, developers...) connecting to your database with SSMS. They will use 10 logins.

    Now let's say app uses same database. Your app will probably use same SQL login for all app users. So if app connects with same login and you have 100 users using app at same moment, 1 login will be used but 100 sessions.

    So, answer to last question is yes, you can have 1 user and 9,600 sessions.

    But it's probably bad idea to do so. You'll need at least 3 logins - 1 DBA, 1 Dev and 1 App.


    Mustafa Toroman, Azure MVP

    • Marked as answer by JRStern Saturday, November 26, 2016 5:25 PM
    Saturday, November 26, 2016 2:32 PM

All replies

  • Let's say that you have 10 people (DBAs, developers...) connecting to your database with SSMS. They will use 10 logins.

    Now let's say app uses same database. Your app will probably use same SQL login for all app users. So if app connects with same login and you have 100 users using app at same moment, 1 login will be used but 100 sessions.

    So, answer to last question is yes, you can have 1 user and 9,600 sessions.

    But it's probably bad idea to do so. You'll need at least 3 logins - 1 DBA, 1 Dev and 1 App.


    Mustafa Toroman, Azure MVP

    • Marked as answer by JRStern Saturday, November 26, 2016 5:25 PM
    Saturday, November 26, 2016 2:32 PM
  • Mustafa,

    Thanks.  I just had trouble believing this was even the situation.  200 *logins* is a lot of logins, since most apps do use connection pools.  And 9600 sessions is about 9,000 more than I've ever run on a server, and maybe about 8,900 more than I'd ever want to run.

    Yet it turns out I have a current situation where these numbers may matter.  Actually, I guess we're using the same login for as many web servers as we have configured for Azure, I believe that's four.  So with default connection pools of 100, we'll have one login, 400 sessions?  Or does each server count as a different login, even if it uses the same username?

    I guess it doesn't matter too much, since we're far below the limits.

    Thanks,

    Josh

    Saturday, November 26, 2016 5:29 PM
  • Even if it doesn't matter, it would be 4 logins and 400 sessions, 100 sessions each.

    And I agree, in most situatuons it's enough but there are some that it isn't. Let's say we have online store with tens of thousands of users. In this case we would make multiple instances of our app and have many more logins and sessions.


    Mustafa Toroman, Azure MVP

    Saturday, November 26, 2016 5:42 PM
  • Mustafa, thanks again, I think I'm clear now on the meanings.

    I guess I will have to learn a bit more about the mechanics, though.  We do want to grow our peak capacity to where we may have to deal with ten thousand or more users concurrently.  Even the full P15 engines might run out of sessions, then we have to shard - or queue.  Or something.

    But much sooner, I have to deal with the situation where we are dealing with, say, 5,000 concurrent users, even if just for a few minutes a day.  I wonder if, in terms of throughput, that's best done with four pools of 1,250 connections.  I've been doing SQL Server for well over ten years, but I've never been on a system that tried to do that - I've done mostly either internal enterprise systems, or public-facing systems ten years ago when the numbers were a lot smaller.

    If we try to load even 5,000 sessions onto a server which after all only has maybe 24 cores (maybe more, but still very much short of 5,000), is that a good thing?  Or is some queuing solution at the app level, using Azure queues or something else, preferable?  SQL Server would have to do a lot of blocking and yielding, with a load much over even a hundred concurrent users.*

    Josh

    *that's concurrent as in instantaneous, I realize 50,000 users in a one-minute interval might only need 500 sessions as long as the workload is all quick transactions.

    Sunday, November 27, 2016 6:06 AM