Request a topic or
contact an Arke consultant
404-812-3123
DotNetNuke

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments

Archive

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2024

DotNetNuke PurgeScheduleHistory

DotNetNuke runs a task to purge it’s schedule history; however, the stored procedure that does this has performance problems that will cause deadlocks on a high traffic website.

The query that the PurgeScheduleHistory stored procedure ships with is:

DELETE FROM dbo.ScheduleHistory
FROM dbo.Schedule s
WHERE (
  SELECT COUNT(*)
  FROM dbo.ScheduleHistory sh with (nolock)
  WHERE sh.ScheduleID = ScheduleHistory.ScheduleID
  AND sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum
AND s.RetainHistoryNum <> -1
AND s.ScheduleID = ScheduleHistory.ScheduleID

Anytime you write a query that does a delete from a select you run the risk of deadlock.  Also, whenever you do a sweeping delete on a table you can escalate to a page lock, have trouble with locking indexes, and end up in a deadlock scenario again.  On the surface this doesn’t sound like a deadlock scenario, but you have to understand the way locks affect indexes and how locks escalate in SQL Server—you might be surprised to learn a simple SELECT query can deadlock under the right conditions also (http://stackoverflow.com/questions/661908/sql-server-deadlocks-between-select-update-or-multiple-selects).

The fix to this stored procedure follows the same approach Microsoft used to fix the DeleteExpiredSessions stored procedure that ships with ASP.NET (http://support.microsoft.com/kb/973849).

The following query should replace the PurgeScheduleHistory stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PurgeScheduleHistory]

AS

SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

create table #T (ID int not null primary key)

insert into #T
select schedulehistoryid from (
select s.ScheduleID, sh.schedulehistoryid, rank() over (partition by s.scheduleid order by sh.startdate) rn, RetainHistoryNum
from ScheduleHistory sh WITH (READUNCOMMITTED)
join Schedule s WITH (READUNCOMMITTED) on s.ScheduleID = sh.ScheduleID
where s.RetainHistoryNum <> -1) a
where rn > RetainHistoryNum

DECLARE ESC CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT ID FROM #T

declare @ID int

open ESC

fetch next from ESC into @ID

WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE FROM ScheduleHistory WHERE ScheduleHistoryID = @ID
        FETCH NEXT FROM ESC INTO @ID
    END

CLOSE ESC

DEALLOCATE ESC

drop table #T

Thanks to David Eison for finding this solution.


Categories: SQL Server | ASP.NET | DotNetNuke
Posted by Eric Stoll on Wednesday, February 16, 2011 1:13 AM
Permalink | Comments (0) | Post RSSRSS comment feed