There is nothing better to start off the day than having a client running into a 100% CPU utilization issue on their production SQL Server. Every few minutes, the server would spike up and hang there for a variable amount of time (15 seconds to several minutes). You can only imagine the flakiness of a website with SQL Server choking to death. There was nothing of value to point any fingers as to the culprit of this issue in the event logs for DNN (Admin > LogViewer)...none that we saw through a brief spot checking and filtering of event types (this was incredibly slow and seeing timeouts so we abandoned all hope of using DNN Admin/Host tools to find the problem). And, there was not an alarming number of events actually logged in EventLog table. However, we have seen issues with performance that are usually resolved by clearing the Log Viewer. We have seen cases where clients who have high traffic/usage sites, or a broken/problematic module on all pages, have 5 and 6 figure rows of data for EventLog table, especially if all the default settings are used for the DNN Log Viewer settings. We have seen timeout issues just trying to clear the event log when they get that large ("Delete EventLog" as the sql statement does the trick quickly from SSMS). So, we went ahead and cleared it, but the issue persisted.
For those who have not explored much in SQL Server Management Studio (not in SSMS Express), there is now a Database Engine Tuning Advisor and SQL Server Profiler (under Tools > SQL Server Profiler). Running the SSP, we performed a trace and caught the offending SQL causing all of the havoc. Just a note...we have run DETA to find recommendations from trace files for several large DNN databases and apply the recommendations (it usually creates new indexes for tables that have 6 and 7 figure rows, helping greatly with performance on databases). But in this case, we just started and stopped the trace in SSP before and after a huge and hanging spike. Going through the rows looking for CPU hits, we found the following 2 villians of resources:
GetSchedule @Server
='SERVERNAME'
GetScheduleNextTask @Server
='SERVERNAME'
Running these statements showed the huge spike on command, pegging the server hard. Looking in the stored proc it hits Schedule and ScheduleHistory.
ALTER
PROCEDURE [dbo].[GetSchedule]
@Server
varchar(150)
AS
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
Schedule S
LEFT
JOIN ScheduleHistory SH
ON
S.ScheduleID = SH.ScheduleID
WHERE
(SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM 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
In ScheduleHistory we found a little over 6 thousand rows. You can use the following to check your db:
select count(*) from schedulehistory
6000+ does not seem like that many rows to be causing that much of a peak, but regardless we deleted them all getting desperate at this point:
delete
schedulehistory
Executing the 2 sprocs again for the schedule, and cpu barely gets over 3% utilization. The site is again fast and responsive and I was able to get in and check settings without getting timeouts. So, as an interim fix I lowered the defaults in DotNetNuke.Services.Scheduling.PurgeScheduleHistory under Host > Schedule.
I am concerned about why 6000 rows of data would be taking such a hit on cpu resources. However, that it more records than I believe should be there, so lowering the defaults will help. Nothing in the DNN stored procedure for GetSchedule really stands out at me as being problematic, nor at first glance do I see anything that could be changed that may help, but I will ponder on this some more in my copious spare time.
So, if you are having trouble with SQL Server performance and DNN, check and make sure you keep your EventLog and ScheduleHistory purged.
If you need help, be sure to checkout our DNN Support Packages.