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.
- An Agent job runs every 5 minutes, writing the counts of sessions for each user to a table
- 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
IF IS_SRVROLEMEMBER('SYSADMIN') = 0
WHERE ORIGINAL_LOGIN_NAME = ORIGINAL_LOGIN()) > 200
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?