Max User Query?

[former_SQLA_member1694868]

How would you figure out what how many users were ever logged in at the same time?

I am trying to come up with some testing scenarios for our software, and I know how many users there are total for a given site, and therefore how many possibly could be logged in at the same time. But I was wondering if there was a counter in the DB that I could query that kept track of how many actually ever were logged in at the same time historically.

[MarkCulp · Accepted solution]

There is no property to tell you this information, but try using a connect event to count the number of users. For example:

create table dba.max_connected_users (
       on_date   date,
       max_users int,
       primary key( on_date )
);

create event dba.Count_Max_Connected_Users
  type Connect
handler
  begin
    declare @num   int;
    declare @today date = today();

    select count(*)
      into @num
      from sa_conn_info()
     where number < 1000000000;

    merge
     into max_connected_users
       as cv( on_date, max_users )
    using ( select @today as on_date,
                   @num   as max_users ) nv
       on cv.on_date = nv.on_date
     when matched then update
      set cv.max_users = if cv.max_users < nv.max_users
                         then nv.max_users
                         else cv.max_users
                         endif
     when not matched then insert;
    commit;
  end;

Once this event is created it will insert/update the max_connected_users table to have one row per day indicating the maximum number of concurrently connected users on each day.

[VolkerBarth · Accepted solution]

What about...

when matched and cv.max_users < nv.max_users then update
when not matched then insert;

[Breck_Carter · Accepted solution]

Catch 22... sa_conn_info() lists connections on all databases, but the Connect event fires only on one database, and the max_connected_users exists on only one database.

[MarkCulp · Accepted solution]

Yep, you are correct. The query that counts the number of connected users would need to be adjusted ... to count what you (as the developer) wants to count. As written it counts the number of users that are connected to the server (which is also what the server counts for its licensing checking... depending on your license type!). If you want to only count the current database then you need to add "AND dbnumber = ( select number from ( select number, db_name(number) as dbname from sa_db_list() where dbname = db_property('name') ) dt )"

[MarkCulp · Accepted solution]

Yep, that would work too.

[VolkerBarth · Accepted solution]

FWIW, according to my understanding of license details (which might be inappropriate...), one could also count "different users" or connections from "different machines":

Some applications will use several connections per user in parallel, say to separate read-only and write access. Therefore one might want to add some kind of grouping to the sa_conn_info() result, say by UserID or NodeAddr.

Just my 2 cents.

[VolkerBarth · Accepted solution]

IMHO, the - small - advantage would be that it

• is shorter and
• would only do an "update" if there's need to.

[former_SQLA_member1694875 · Accepted solution]

Shorter form: "AND dbnumber = DB_ID()"

[MarkCulp · Accepted solution]

Duh! I figured there must be a function that gave the number of the current database but I didn't see it. Thanks.

[former_SQLA_member1694868 · Accepted solution]

Looks good, thanks Mark. I need to spend some more time with Mr. Merge. One of those things that came across as a new feature and I thought was neat but never really practiced.

[VolkerBarth · Accepted solution]

Sounds very common to me - "insert on existing" still looks way easier:)