Poor Performance After DotNetNuke Upgrade

Some folks are experiencing slow response times or poor performance following their latest DNN upgrade. We ran into this and found that the problem was actually a couple of things.

First we noticed some severe pagination of the database and indexes. We performed some much needed index re-builds which significantly improved performance. Lastly, there were circular references in the tabs table. Thanks to Scott Willhite who posted the great little script to check for this. Here is the original post.

DECLARE @TabId INT
DECLARE @PortalId INT
DECLARE @ParentId INT
DECLARE @Level INT
DECLARE @PortalName NVARCHAR(128)
DECLARE
@NewTabId INT
DECLARE @NewParentId INT
DECLARE @MSG NVARCHAR(1000)

DECLARE @PreviousPortalId INT
SET @PreviousPortalId = -999

DECLARE cur CURSOR LOCAL SCROLL STATIC READ_ONLY FOR
SELECT TabId,
PortalId, ParentId, [Level] FROM Tabs ORDER BY PortalId, TabId

OPEN cur
FETCH NEXT FROM cur INTO @TabId, @PortalId, @ParentId, @Level

WHILE
(@@FETCH_STATUS = 0)
BEGIN
IF (ISNULL(@PortalId, -1) <>
@PreviousPortalId)
BEGIN
PRINT
'================================================='
SELECT @PortalName =
PortalName FROM Portals WHERE PortalId = @PortalId
PRINT 'Processing Portal:
' + ISNULL(@PortalName,'No Portal, Host Menu')
PRINT
'================================================='
END

SET
@NewParentId = @ParentId
SET @NewTabId = @TabId
PRINT 'Validating Tab ['
+ LTRIM(CAST(@TabId AS VARCHAR(10))) + '] :'
WHILE (1 = 1)
BEGIN
SET
@MSG = CHAR(9) + LTRIM(CAST(@NewTabId AS VARCHAR(10))) + ' >> '
IF
@NewParentId IS NULL
BEGIN
SET @MSG = @MSG + '[NULL]... OK'
PRINT
@MSG
BREAK
END
ELSE
BEGIN
SET @MSG = @MSG +
LTRIM(CAST(@NewParentId AS VARCHAR(10))) + '... '
END
SELECT @NewTabId =
TabId, @NewParentId = ParentId FROM Tabs WHERE TabId = @NewParentId

PRINT @MSG
END

SET @PreviousPortalId = @PortalId
FETCH
NEXT FROM cur INTO @TabId, @PortalId, @ParentId, @Level
END
CLOSE cur
DEALLOCATE cur