Post reply

MARS, in-memory tables, and a server-level trigger

  • March 31, 2021 at 8:07 pm

    #3864144

    Trying to cut down on some very minor occasional contention on my servers.  We're required to limit the number of simultaneous connections from any given login and the way we've gone about it is a 2-part method.

    1. An Agent job runs every 5 minutes, writing the counts of sessions for each user to a table
    2. A server trigger fires on every login, reads the table and count, and if the max is exceeded, rolls back the login

    So far, it's worked, but I'm looking to improve things, as I've occasionally seen some slowdowns during heavy use.  So I started down the in-memory table rabbit hole.  Modifying the Agent job from a MERGE to an UPSERT was easy enough, setting up for in-memory, also easy enough.

    But today, one of our customers was getting blocked, and the SQL logs were reporting an issue with in-memory and MARS...

    I've asked them to check their applications connection string when they have a moment to see if they've got MARS enabled (or at least, not explicitly disabled,) but I want to rule out the trigger code as well.

    So, here's the trigger:

    CREATE TRIGGER [CONNLIMIT]
      ON ALL SERVER
      FOR LOGON
    AS
    BEGIN
      IF IS_SRVROLEMEMBER('SYSADMIN') = 0
        IF
          (SELECT CONNECTIONS
           FROM DBATools.DBO.CONNECTIONCOUNT
           WHERE ORIGINAL_LOGIN_NAME = ORIGINAL_LOGIN()) > 200
        ROLLBACK;
    END;

    I've not seen anything to indicate that this should be using MARS (and, on servers that have had some issues in the past with this, I did a bad thing, I added a WITH (nolock) to work around it)

    The is_srvrolemember check is to avoid locking out the sysadmin (me) although I've not gotten close to the connection limit.  So, thoughts, comments, suggestions?

  • April 1, 2021 at 8:10 pm

    #3864550

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply