Some DNN sites spend way too much time running the sproc dbo.GetSchedule. This is probably worse if DNN is configured with its Scheduled Jobs in the default ‘Request’ mode (instead of ‘Timer’ mode). Unfortunately that job is both slow and can deadlock on updates.
The original job we had in our DNN 5.6.1 is doing:
SELECT
S.*,
SH.NextStart
FROM dbo.Schedule S
LEFT JOIN dbo.ScheduleHistory SH ON S.ScheduleID = SH.ScheduleID
WHERE (SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID
FROM dbo.ScheduleHistory S1
WHERE S1.ScheduleID = S.ScheduleID
ORDER BY S1.NextStart DESC)
OR SH.ScheduleHistoryID IS NULL)
AND (@Server IS NULL OR S.Servers LIKE '%,' + @Server + ',%' OR S.Servers IS NULL)
Here’s almost the same thing, but faster and less likely to deadlock:
SELECT
S.*,
(SELECT TOP 1 NextStart FROM ScheduleHistory S1 with(nolock)
WHERE S1.ScheduleID = S.ScheduleID
ORDER BY S1.NextStart DESC) as NextStart
FROM dbo.Schedule S with(nolock)
WHERE (@Server IS NULL OR S.Servers LIKE '%,' + @Server + ',%' OR S.Servers IS NULL)
Replacing this one query dropped one problematic DNN site from 100% sql server cpu utilization to more in the 30% range.