Limiting Specific User Connections

  • RonMexico

    Hall of Fame

    Points: 3340

    Our environment has a set of users that use a third party app to connect to SQL Server. The problem is that these users like to open up multiple connections and run concurrent queries against the data which sometimes can hinder performance. I would like to limit the amount of connections this group can make and my first thought it to have a trigger.

    CREATE TRIGGER ConnectionLimit
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
    IF ORIGINAL_LOGIN()= 'TestUser'
    AND (SELECT COUNT(1)
    FROM sys.dm_exec_sessions
    WHERE is_user_process = 1
    And original_login_name = ORIGINAL_LOGIN()
    ) > 4
    BEGIN
    PRINT 'The login [' + ORIGINAL_LOGIN() + '] has exceeded the concurrent session limit.'
    ROLLBACK;
    END
    END;

    This would cap the user(s) at four connections. I'm curious to see what other people think about this method and if there is a better way to achieve limiting the connections. Is there anything else I should watch out for if I go this route?

  • Jeff Moden

    SSC Guru

    Points: 996622

    If you have the enterprise edition, you could use the Resource Governor.  I've not done a deep read on the trigger you wrote but that would also work.

    You should give people a heads up as to what you're doing and why, though.  Perhaps you could take a look at people's queries from the app and help them make it so that they run faster and use fewer resources.  After all, they're trying to do THEIR JOB and it's up to people like us to help them do it.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • RonMexico

    Hall of Fame

    Points: 3340

    I'll take a look at the resource governor as well. Thanks for the tip.

    Previous communication to limit the number of connections hasn't worked. Some users run up to 20 at a time and it isn't so much poorly written queries as it is the amount of data being queried. I'm more or less looking at this option and the feasibility of it in case it has to come to it.

     

  • Andrew P

    SSCarpal Tunnel

    Points: 4493

    It sounds like you're on the right path - seeing this as a final option given that this behavior is severely impacting the business. I think it will work.

    I second that Resource Governor would be a preferable option - that would give them a pool of resource limits, and would throttle them if they hit it, instead of actively giving them application error messages and forcing a response.

    Is there potential to use this to request increased server resources/hardware to cope with the increased load, or if those queries are going parallel and using high CPU, can you reduce MAXDOP to reduce their impact on other queries? This would affect queries from other users/applications too.

    Consider whether the end-user having 20 windows open is most efficient for delivering business value. If it is, then you're adding a constraint to that process, and that needs to be best for the whole business. This method will also force a response when new sessions fail. Cap this as high as you can tolerate.

    Keep communicating to those users that you have reviewed the alternatives, and that this genuinely is the most appropriate decision for the business.

    It sounds like you have already, but be sure you understand the application behavior and are only cutting the high-resource use sessions. For example, SQL Server Management Studio has one open session for Intellisense, one for the Object Explorer, and one for each open query tab.

    This is one I highly suggest testing first - I've locked everyone out of a development instance before through a bad server trigger!

    • This reply was modified 2 months, 3 weeks ago by  Andrew P.

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

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