Has anyone else noticed that in 5.1.4 the scheduler is beating the heck out of the database? The following 2 queries are the top database resource consumers. I have yet to analyze them to see what indexes / optimizations need to be added to make them a bit more reasonable. Note my schedule history table has about 40K rows. But still that's not really all that many!
These queries are using 90 CPU ms / sec & 76 CPU ms / sec !!! and they run 6 times a minute. the next highest contender is 2 CPU ms / sec and it runs 308 times / minute.
Looking quickly at the execution plans, it seems that the final sort/grouping of each of the queries accounts for more than 75% if the total execution cost. Anyone have some thoughts?
SELECT S.ScheduleID,
S.TypeFullName,
S.TimeLapse,
S.TimeLapseMeasurement,
S.RetryTimeLapse,
S.RetryTimeLapseMeasurement,
S.ObjectDependencies,
S.AttachToEvent,
S.RetainHistoryNum,
S.CatchUpEnabled,
S.Enabled,
SH.NextStart,
S.Servers
FROM dbo.DNN_Schedule S
LEFT JOIN dbo.DNN_ScheduleHistory SH ON S.ScheduleID = SH.ScheduleID
WHERE (SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM dbo.DNN_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)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement, S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart, S.Servers
SELECT TOP 1 S.ScheduleID,
S.TypeFullName,
S.TimeLapse,
S.TimeLapseMeasurement,
S.RetryTimeLapse,
S.RetryTimeLapseMeasurement,
S.ObjectDependencies,
S.AttachToEvent,
S.RetainHistoryNum,
S.CatchUpEnabled,
S.Enabled,
SH.NextStart
FROM dbo.DNN_Schedule S
LEFT JOIN dbo.DNN_ScheduleHistory SH ON S.ScheduleID = SH.ScheduleID
WHERE ((SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM DNN_ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC) OR SH.ScheduleHistoryID IS NULL) AND S.Enabled = 1)
AND (S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement, S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart
ORDER BY SH.NextStart ASC