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:)

[MCMartin]

You can have a look at the file sadiags.xml in this you find an entry maxconcurconn, as Mark stated: "...records the maximum concurrent client connections that was seen during the database server's up-time..."

see also question: Any documentation for sadiags.xml?

[VolkerBarth]

That's a nice hint - but how to analyze these numbers in the "maxconcurconn" node?

<F N="0" D="0" M="1,6,0,2,0,0,1,0,1,0,0,0,0,1,0,0,0,0,2,0,1" />
<F N="1" D="1,1" M="2,3,3,2,5,0,2,0,0,1,1,3,1,1,0,0,1,4,7,1,1,1,1" />

(Yes, my somewhat rash comment on your cited question - "self-documenting XML" - hits back...)

[MCMartin]

My understanding is, that N is the number of concurrent connections, but maybe Mark can give use more insight

[MarkCulp]

In the above two posted examples the N="0" and N="1" specify the maximum number of concurrent connections and the D-list and M-list of numbers refer to the number of times that that maximum was hit in the previous days and months respectively. Note that the days and months are relative to the date specified at the top of the file in the S tag. HTH

[VolkerBarth]

@Mark: Thanks for the clarification!

I should add, this excerpt is taken from a rarely-used test server on my box, and there were further entries with N > 1:)

[Breck_Carter]

An old-school solution for the maximum number of connections to this database...

CREATE TABLE max_connected_users ( 
   on_date         DATE NOT NULL PRIMARY KEY,
   max_connections INTEGER NOT NULL );

CREATE EVENT record_max_connections TYPE CONNECT HANDLER
BEGIN

INSERT max_connected_users
   ON EXISTING SKIP
   VALUES ( CURRENT DATE, 0 );

UPDATE max_connected_users
      SET max_connections = GREATER ( max_connections, DB_PROPERTY ( 'ConnCount' ) )
    WHERE on_date = CURRENT DATE;

END;

[MarkCulp]

Note that the ConnCount database property counts more than just user connections - it includes event connections and external environment connections.

[VolkerBarth]

@Breck: "Old-school" - so you don't code MERGE all the day? 🙂

[Breck_Carter]

Someday, I will code a MERGE...