We recently ran into an issue with the nightly scheduled SolarWinds DB maintenance utility taking a long time to complete and seeming to zone out for two blocks of time - once for 1 hour and later for 3-5 hours. After further investigation this was determined to be when running the stored procedures dbm_Pollers_DeleteOrphans and dbm_Pollers_Interfaces_DeleteOrphans, respectively.
We were able to resolve the issue by splitting each DELETE statement with dual IN clauses (linked by OR) into 2 DELETE statements with a single IN clause each. For example, in dbm_Pollers_Interfaces_DeleteOrphans:
DELETE FROM Pollers
WHERE (NetObjectType = 'I' OR NetObjectType = 'IW')
AND (
NetObjectID IN (SELECT InterfaceId FROM DeletedInterfaces)
OR NetObjectID NOT IN (SELECT InterfaceId FROM Interfaces)
)
becomes:
DELETE FROM Pollers
WHERE (NetObjectType = 'I' OR NetObjectType = 'IW')
AND (NetObjectID IN (SELECT InterfaceId FROM DeletedInterfaces))
DELETE FROM Pollers
WHERE (NetObjectType = 'I' OR NetObjectType = 'IW')
AND (NetObjectID NOT IN (SELECT InterfaceId FROM Interfaces))
Unless we've misconfigured SQL Server in some non-obvious way, it would be good to get this change into the next update. We are using Orion 2018.4 HF3 with NPM 12.4 and SAM 6.8.0 and SQL Server 2016, but this likely won't be an issue unless the system has a high number of elements (we're dealing with about 225,000). In our case, this dropped the run time of both stored procedures from hours down to less than 5 minutes.
