Community,
I have been experiencing a large number of system down incidents (29 as of yesterday) over the past couple months. These are caused by SQL deadlocks. I need assistance determining root cause. Before you ask, Orion is completely updated, SQL server 2016, 12 cpu cores per VM, and minimum 128MB RAM. These servers are fully updated and overpowered. The queries are the same every time, pulling from the events table. Any assistance or guidance would be appreciated. Please do not ask me to put in a support ticket, I already have two with no progress made, including one for system down right now (been on hold too long).
Blocking Session (From Primary Server):
Application name: SolarWinds.Collector.Service@domain-SWCollectorService-0
First 2KB of SQL: (@engineId int,@eventType int,@eventDate int)SELECT MAX(e.EventTime), e.NetObjectID FROM Events e
INNER JOIN Nodes n ON n.NodeID = e.NetObjectID
WHERE n.EngineID = @engineId AND e.EventType = @eventType AND e.EventTime >= DATEADD(D, -@eventDate, GETDATE()) GROUP BY e.NetObjectID
Blocked session (From additional poller):
Application name: SolarWinds.Collector.Service@domain-SWCollectorService-0
First 2KB of SQL:
CREATE PROCEDURE [dbo].[apm_AddEvent]
@NetworkNode int,
@NetObjectID int,
@NetObjectType char(10),
@EventType int,
@Message ntext,
@EventTimeUtc datetime = NULL
AS
BEGIN
IF ((@EventType IS NOT NULL) AND ((@Message IS NOT NULL) AND (DATALENGTH(@Message) > 0)) AND ((Select Record From EventTypes where EventType = @EventType) = 1))
BEGIN
SET NOCOUNT ON;
DECLARE @EventTimeLocal datetime
IF @EventTimeUtc IS NULL
SET @EventTimeLocal = GETDATE()
ELSE
SET @EventTimeLocal = DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @EventTimeUtc)
INSERT INTO Events (EventTime, NetworkNode, NetObjectID, NetObjectType, EventType, Message, Acknowledged)
VALUES (@EventTimeLocal, @NetworkNode, @NetObjectID, @NetObjectType, @EventType, @Message, 0)
END
END
