We have a custom SQL query on availability we have been running for years for a client with 145 locations. It use to run in a matter of seconds till we upgraded to NPM 12.4 and SQL server 2016 SP2. Now the query runs for hours for more than 10 nodes. It will run in 4 seconds for 10 nodes or less. I have included the query that the reporting team is using in hopes someone can shed a light on what appears to have changed since upgrade.
SELECT Nodes.Caption AS Device_Name,
av.[Availability] AS [Availability],
downevents.cnt AS Down_Events
FROM Nodes
OUTER APPLY (
SELECT AVG([Availability]) AS [Availability]
FROM (
SELECT [NodeID],
[Timestamp],
[ResponseTime],
[PercentLoss],
[Availability],
[Weight]
FROM ResponseTime_CS_cur
WHERE [Timestamp] BETWEEN CONVERT(datetime2, '2019-05-07') AND CONVERT(datetime2, '2019-05-08')
AND NodeID = Nodes.NodeID
UNION SELECT [NodeID],
[Timestamp],
[ResponseTime],
[PercentLoss],
[Availability],
[Weight] FROM ResponseTime_CS_Detail_hist
WHERE Timestamp BETWEEN CONVERT(datetime2, '2019-03-24') AND CONVERT(datetime2, '2019-03-25')
AND NodeID = Nodes.NodeID
) AS ResponseData
) AS av
OUTER APPLY (
SELECT COUNT(*) AS cnt FROM [Events]
WHERE NetObjectID = Nodes.NodeID AND EventType = 1
AND [EventTime] BETWEEN '2019-04-4' AND '2019-04-5') AS downevents
Where nodes.nodeid >2 and nodes.nodeid <16
