I am a DBA who supports an application using a DNN database. Last week, our production cluster showed CPU usage spiking to 100% for several seconds every 30 seconds, which I traced to DNN's GetScheduledNextTask stored procedure. Our developer then simply truncated the ScheduleHistory table and everything is working smoothly. Discontent on that option as a DBA, I delved further into the issue and found the performance of the current stored procedure appallingly abysmal.

The current stored procedure is this:

SELECT TOP 1 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 S.Enabled = 1)
AND (S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
ORDER BY SH.NextStart ASC

The subquery's entire performance is directly dependent on the underlying dataset being extremely small, which is a horrible assumption to make -- any good DBA will tell you that.

Compare the execution plan of the current stored procedure with the execution plan of the stored procedure optimized:

SELECT TOP 1
[Schedule].[ScheduleID],
[Schedule].[TypeFullName],
[Schedule].[TimeLapse],
[Schedule].[TimeLapseMeasurement],
[Schedule].[RetryTimeLapse],
[Schedule].[RetryTimeLapseMeasurement],
[Schedule].[RetainHistoryNum],
[Schedule].[AttachToEvent],
[Schedule].[CatchUpEnabled],
[Schedule].[Enabled],
[Schedule].[ObjectDependencies],
[Schedule].[Servers],
[Schedule].[CreatedByUserID],
[Schedule].[CreatedOnDate],
[Schedule].[LastModifiedByUserID],
[Schedule].[LastModifiedOnDate],
[Schedule].[FriendlyName],
[ScheduleHistory].[NextStart]
FROM
[dbo].[Schedule]
CROSS APPLY (
SELECT TOP 1
[NextStart]
FROM
[dbo].[ScheduleHistory]
WHERE
([ScheduleID] = [Schedule].[ScheduleID])
ORDER BY
[NextStart] DESC
) AS [ScheduleHistory]([NextStart])
WHERE
([Schedule].[Enabled] = 1)
AND (([Schedule].[Servers] LIKE (',%' + @Server + '%,')) OR ([Schedule].[Servers] IS NULL))
ORDER BY
[ScheduleHistory].[NextStart] ASC

This has been tested in SQL Server 2005 and SQL Server 2008 R2.