Logon Trigger suddenly caused spike in query time
-
We have a logon trigger that has been on our server for a couple months now with no issue. It blocks login attempts from application accounts that are not from certain web servers using the host_name field in dm_exec_sessions.
Yesterday afternoon we experienced higher than usual traffic, and I received a notification that a login attempt was blocked due to trigger execution from a whitelisted server. This is strange in itself, because I don't see why a login attempt would be blocked if the host_name is whitelisted. Around the same time, the SQL Server started behaving very slowing, and taking a long time to complete operations.
The Lock Timeouts/sec rose greatly from 0.16 to over 2 K and the Average Latch Wait Time also increased exponentially. We also observed that the number of pooled connection also greatly rose, almost doubling. As soon as we disabled the trigger everything went back to normal, and the server started running smoothing again.
I've been reading about connection pooling
https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/10/08/connection-pooling-for-the-sql-server-dba/#comment-18155
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#clearing-the-pool, but I can't figure out what the trigger has to do with all of this and why it suddenly started causing the slow down. Any ideas?
- NC1 - Tuesday, November 28, 2017 12:16 PM
Can you post the trigger code please?
-
By any chance, do the connection strings have MARS enabled?
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, November 29, 2017 6:48 AM
Good point Jeff, then throw distributed transaction coordination in the mix and it's a proper recepie for a disaster, most developers fail to understand the escalation path defined in the connection string.
- Eirikur Eiriksson - Wednesday, November 29, 2017 7:05 AMJeff Moden - Wednesday, November 29, 2017 6:48 AM
Good point Jeff, then throw distributed transaction coordination in the mix and it's a proper recepie for a disaster, most developers fail to understand the escalation path defined in the connection string.
Worse yet, the documentation claims that MARS is off by default and yet we have indications that the opposite may be true.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, November 29, 2017 8:02 AMEirikur Eiriksson - Wednesday, November 29, 2017 7:05 AMJeff Moden - Wednesday, November 29, 2017 6:48 AM
Good point Jeff, then throw distributed transaction coordination in the mix and it's a proper recepie for a disaster, most developers fail to understand the escalation path defined in the connection string.
Worse yet, the documentation claims that MARS is off by default and yet we have indications that the opposite may be true.
Yes, this is true but I have the recollection of this being partially down to the likes of Entety Framework defaults etc. Will have to look through my notes, researched this in details few years back.
- Eirikur Eiriksson - Wednesday, November 29, 2017 8:17 AMJeff Moden - Wednesday, November 29, 2017 8:02 AMEirikur Eiriksson - Wednesday, November 29, 2017 7:05 AMJeff Moden - Wednesday, November 29, 2017 6:48 AM
Good point Jeff, then throw distributed transaction coordination in the mix and it's a proper recepie for a disaster, most developers fail to understand the escalation path defined in the connection string.
Worse yet, the documentation claims that MARS is off by default and yet we have indications that the opposite may be true.
Yes, this is true but I have the recollection of this being partially down to the likes of Entety Framework defaults etc. Will have to look through my notes, researched this in details few years back.
Ah... sorry. I meant to say that the default stuff I was talking about was, indeed, related to Entity Framework.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, I spoke with one of my developers and he said they do indeed have MARS enabled, at least for a majority of the connection strings. I'm reading about this feature now, which seems like it would reduce the number of connections needed to SQL Server.
Eirikur, DTC isn't involved with this server, but here is a sample of the code for the trigger:
CREATE TRIGGER [Kill_Client_Sessions]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT login_name, host_name, program_name, *
FROM sys.dm_exec_sessions
WHERE login_name LIKE 'rrr%'
AND (host_name NOT LIKE 'xxx%'
AND host_name NOT LIKE 'yyy%'
AND host_name NOT LIKE 'zzz%'
AND host_name NOT LIKE 'qqq%'))
ROLLBACK;
END;
GO - NC1 - Wednesday, November 29, 2017 8:29 AM
First suggestion is to use the internal sys.SYSSESSIONS session_id column rather than the * in the exists subquery, the next suggestion is to use a firewall to do this job as that's far more efficient than a logon trigger!
Frankly, you need to redesign this!
-
Thanks for the reply Eirikur,
I agree that the * is not necessary in the Select, but I'm not sure I understand the use of SYSSESSIONS; it looks like that view is concered with SQL Agent sessions. I will look into using a firewall for this purpose, I wasn't there for the initial discussion of this solution, so I'm not sure about the reason we chose this instead of a network based solution.
I'm still confused though on why the trigger would cause this issue in the first place.
- NC1 - Wednesday, November 29, 2017 8:29 AM
They should turn MARS OFF on all connection strings pointing at the SQL SERVER unless there's are very specific application for it. Having it on absolutely crushed us, as I said in my previous post.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply