Question Re: Max User Query? in Technology Q&A

Max User Query?

former_SQLA_member1694868 — Wed, 21 Mar 2012 11:52:34 GMT

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.

Re: Max User Query?

MarkCulp — Wed, 21 Mar 2012 13:12:32 GMT

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.

Re: Max User Query?

VolkerBarth — Wed, 21 Mar 2012 13:21:41 GMT

What about...

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

Re: Max User Query?

Breck_Carter — Wed, 21 Mar 2012 13:53:55 GMT

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.

Re: Max User Query?

Breck_Carter — Wed, 21 Mar 2012 14:07:25 GMT

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;

Re: Max User Query?

MarkCulp — Wed, 21 Mar 2012 14:24:15 GMT

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

Re: Max User Query?

MarkCulp — Wed, 21 Mar 2012 14:29:45 GMT

Yep, that would work too.

Re: Max User Query?

MarkCulp — Wed, 21 Mar 2012 14:47:05 GMT

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

Re: Max User Query?

VolkerBarth — Thu, 22 Mar 2012 04:32:37 GMT

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.

Re: Max User Query?

VolkerBarth — Thu, 22 Mar 2012 04:34:59 GMT

IMHO, the - small - advantage would be that it

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

Re: Max User Query?

VolkerBarth — Thu, 22 Mar 2012 04:35:35 GMT

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

Re: Max User Query?

former_SQLA_member1694875 — Thu, 22 Mar 2012 05:13:48 GMT

Shorter form: "AND dbnumber = DB_ID()"

Re: Max User Query?

MCMartin — Thu, 22 Mar 2012 06:05:36 GMT

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?

Re: Max User Query?

VolkerBarth — Thu, 22 Mar 2012 08:14:06 GMT

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

Re: Max User Query?

MarkCulp — Thu, 22 Mar 2012 08:20:18 GMT

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

Re: Max User Query?

MCMartin — Thu, 22 Mar 2012 08:50:08 GMT

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

Re: Max User Query?

MarkCulp — Thu, 22 Mar 2012 09:20:26 GMT

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

Re: Max User Query?

former_SQLA_member1694868 — Thu, 22 Mar 2012 09:41:31 GMT

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.

Re: Max User Query?

VolkerBarth — Thu, 22 Mar 2012 10:09:57 GMT

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

Re: Max User Query?

VolkerBarth — Thu, 22 Mar 2012 10:10:58 GMT

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