April 10, 2020 at 3:34 pm
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?
April 10, 2020 at 4:08 pm
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
Change is inevitable... Change for the better is not.
April 10, 2020 at 5:53 pm
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.
April 11, 2020 at 12:20 am
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply