We ran into an issue today where the development SQL server was being CRUSHED and after investigating we found out it was a stored procedure call to GetSchedule from the scheduler subsystem inside DNN.

There were only like 9 records in the scheduler table and 14K in the history table so we could not understand what the huge performance issue was. Running the proc in query analyzer, it was taking over 5 minutes to complete!

Long story short, what the issue appear to be is the query does a correlated sub-query on the history table and indexes on the ScheduleHistory table have not been optimized to deal with this correlated sub-query logic.

Here is what I did to fix it and I’m looking for feedback from others that either have developed on this subsystem or know about to make sure there are not any other side effects that this change could make.

I took the ScheduleHistory table and changed the primary key ScheduleHistoryID to be a non-clustered index. I then added a non-clustered index to the foreign key field ScheduleID, and a clustered index to the NextStart field.

What used to take 5 minutes to run now takes ~1 second!

Any side effects anyone can think of doing this?

If not, if someone could point the core scheduler developers to this thread and suggest this as an update to the core framework, that would be great. For sure the foreign key needs to be indexed at a minimum in my opinion since its used in all join operations.

Thx!

Ross Nornes
TSQL Script I Used To Alter Table

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.ScheduleHistory
    DROP CONSTRAINT PK_ScheduleHistory
GO
ALTER TABLE dbo.ScheduleHistory ADD CONSTRAINT
    PK_ScheduleHistory PRIMARY KEY NONCLUSTERED
    (
    ScheduleHistoryID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX idx_NextStart ON dbo.ScheduleHistory
    (
    NextStart
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX idx_ScheduleID ON dbo.ScheduleHistory
    (
    ScheduleID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT