If PortalID is provided, UpdateUser writes to both Users and UserPortals (query below).
It is difficult to see from the trace which update causes the timeout.
In this system, there is only one active portal (PortalID = 2).
Both tables have clustered indexes on the primary key columns, and UpdateUser looks up a row from both tables by primary key. Indexes are not fragmented and they are rebuilt regularly. There should be no way to improve this update by indexing more.
On the contrary, deleting some of the DNN's default nonclustered indexes on these tables might help, but I don't want to mess with the platform.
During the timeouts, it seems that any query from Users or UserPortals takes more than the 30s default for timeout. It is just this UpdateUser procedure that is executed most often.
CREATE PROCEDURE [dbo].[UpdateUser]
@UserID int,
@PortalID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@IsSuperUser bit,
@Email nvarchar(256),
@DisplayName nvarchar(100),
@UpdatePassword bit,
@Authorised bit,
@RefreshRoles bit,
@LastIPAddress nvarchar(50),
@IsDeleted bit,
@LastModifiedByUserID int
AS
UPDATE dbo.Users
SET
FirstName = @FirstName,
LastName = @LastName,
IsSuperUser = @IsSuperUser,
Email = @Email,
DisplayName = @DisplayName,
UpdatePassword = @UpdatePassword,
LastIPAddress = @LastIPAddress,
LastModifiedByUserID = @LastModifiedByUserID,
LastModifiedOnDate = getdate()
WHERE UserId = @UserID
IF @PortalID IS NULL
BEGIN
UPDATE dbo.Users
SET
IsDeleted = @IsDeleted
WHERE UserId = @UserID
END
ELSE
BEGIN
UPDATE dbo.UserPortals
SET
Authorised = @Authorised,
RefreshRoles = @RefreshRoles,
IsDeleted = @IsDeleted
WHERE UserId = @UserID
AND PortalId = @PortalID
END