This seems to be taking over 5 hours
2015-07-30 10:50:34
This seems to be taking over 5 hours – can it be optimized?
I've added the missing index:
CREATE NONCLUSTERED INDEX [] ON [dbo].[tblSystemAuditTrail] ([AuditTrailID],[AuditTrailTypeID]) INCLUDE ([UserID],[ApplicationID],[EntityID],[EntityRefID],[ActionDate],[CommentID])
Query:
SET NOCOUNT ON DECLARE @MaxID int SELECT @MaxID = MaxID FROM tblTableRefreshMaxID WHERE TableName = 'tblSystemAuditTrail' --INSERT tblAuditUpdates (EntityID, EntityRefID, ActionDate, UserID, ApplicationID, FieldID, OldValue, NewValue, Comment) SELECT a.EntityID, a.EntityRefID, a.ActionDate, a.UserID, a.ApplicationID, d.FieldID, d.OldValue, d.NewValue, c.Comment FROM ESS.dbo.tblSystemAuditTrail a (nolock) INNER JOIN ESS.dbo.tblSystemAuditTrailDetail d (nolock) ON a.AuditTrailID = d.AuditTrailID LEFT OUTER JOIN ESS.dbo.tblDataComment c ON a.CommentID = c.CommentID WHERE a.AuditTrailID > @MaxID AND a.AuditTrailTypeID IN (3, 6) --UPDATE tblTableRefreshMaxID --SET MaxID = (SELECT MAX(AuditTrailID) FROM ESS.dbo.tblSystemAuditTrail) --WHERE TableName = 'tblSystemAuditTrail'
2015-08-01 08:40:46
The main problem is the sort (required for a merge join):
A very likely better plan would use nested loops. The index needed to support that is very close to the suggested missing index:
CREATE UNIQUE INDEX i ON dbo.tblSystemAuditTrail (AuditTrailTypeID, AuditTrailID) INCLUDE (EntityID, EntityRefID, ActionDate, UserID, ApplicationID, CommentID);
That reverses the key order, so the equality comparison comes before the inequality.
On my simulation of your tables, that produces the following plan:
You may need to use an OPTION (LOOP JOIN) hint to get this plan, depending on statistics, and your settings for server max memory & max degree of parallelism.
You should also consider updating your SQL Server to at least Service Pack 3 from the current SP1.