Guidance in reading SQL Server Explain plan

Question

We have a SQL Server 2008 Express server that we are currently attempting to troubleshoot performance issues. The explain plan shows where the query is hitting indexes, however I would like to improve more if I could. I attached the hover box from a Hash Match. The server is not suggesting any missing indexes, however I want to attempt to find any that might improve performance.

SELECT ROW_NUMBER() OVER (ORDER BY
    CASE WHEN @Sort = 'RoutedItemID' THEN DocumentRoute.DocumentRouteID ELSE NULL END DESC,
    CASE WHEN @Sort = 'RouteID desc' THEN Routes.RouteID ELSE NULL END DESC,
    CASE WHEN @Sort = 'RouteStepID desc' THEN DocumentRoute.RouteStepID ELSE NULL END DESC,
    CASE WHEN @Sort = 'CallingRoutedItemID desc' THEN DocumentRoute.CallingDocRouteID ELSE NULL END DESC,
    CASE WHEN @Sort = 'ParentRoutedItemID desc' THEN ParentDocRouteID ELSE NULL END DESC,
    CASE WHEN @Sort = 'SiblingRoutedItemID desc' THEN SiblingDocRouteID ELSE NULL END DESC,
    CASE WHEN @Sort = 'RoutedObjectID desc' THEN DocumentRoute.DocumentID ELSE NULL END DESC,
    CASE WHEN @Sort = 'RoutedObjectType desc' THEN DocumentRoute.ItemType  ELSE NULL END DESC,
    CASE WHEN @Sort = 'UserID desc' THEN DocumentRoute.UserID ELSE NULL END DESC,
    CASE WHEN @Sort = 'ByUserID desc' THEN DocumentRoute.ByUserID ELSE NULL END DESC,
    CASE WHEN @Sort = 'Status desc' THEN DocumentRoute.Status ELSE NULL END DESC,
    CASE WHEN @Sort = 'FinalStep desc' THEN DocumentRoute.Finished ELSE NULL END DESC,
    CASE WHEN @Sort = 'Completed desc' THEN DocumentRoute.Completed ELSE NULL END DESC,
    CASE WHEN @Sort = 'StartDate desc' THEN DocumentRoute.StartDate ELSE NULL END DESC,
    CASE WHEN @Sort = 'DueDate desc' THEN DocumentRoute.DueDate ELSE NULL END DESC,
    CASE WHEN @Sort = 'OriginatorID desc' THEN DocumentRoute.OriginatorID ELSE NULL END DESC,
    CASE WHEN @Sort = 'RouteWait desc' THEN DocumentRoute.RouteWait ELSE NULL END DESC,
    CASE WHEN @Sort = 'Direction desc' THEN DocumentRoute.Direction ELSE NULL END DESC,
    CASE WHEN @Sort = 'ProcessFlag desc' THEN DocumentRoute.ProcessFlag ELSE NULL END DESC,
    CASE WHEN @Sort = 'ProcessDate desc' THEN DocumentRoute.ProcessDate ELSE NULL END DESC,
    CASE WHEN @Sort = 'RelFileID desc' THEN Documents.FileID ELSE NULL END DESC,
    CASE WHEN @Sort = 'Divider DividerName desc' THEN Documents.DividerName ELSE NULL END DESC,
    CASE WHEN @Sort = 'Separator desc' THEN Documents.Separator ELSE NULL END DESC,
    CASE WHEN @Sort = 'BatchDate desc' THEN Documents.BatchDate ELSE NULL END DESC,
    CASE WHEN @Sort = 'Extension desc' THEN Documents.Extension ELSE NULL END DESC,
    CASE WHEN @Sort = 'StepNumber desc' THEN RouteSteps.StepNumber ELSE NULL END DESC,
    CASE WHEN @Sort = 'StepName desc' THEN RouteSteps.StepName ELSE NULL END DESC,
    CASE WHEN @Sort = 'PromptComplete desc' THEN RouteSteps.PromptComplete ELSE NULL END DESC,
    CASE WHEN @Sort = 'PromptReject desc' THEN RouteSteps.PromptReject ELSE NULL END DESC,
    CASE WHEN @Sort = 'PromptUser desc' THEN RouteSteps.PromptUser ELSE NULL END DESC,
    CASE WHEN @Sort = 'ShowButtonComplete desc' THEN RouteSteps.ShowButtonComplete ELSE NULL END DESC,
    CASE WHEN @Sort = 'ShowButtonReject desc' THEN RouteSteps.ShowButtonReject ELSE NULL END DESC,
    CASE WHEN @Sort = 'ShowButtonReassign desc' THEN RouteSteps.ShowButtonReassign ELSE NULL END DESC,
    CASE WHEN @Sort = 'Authenticate desc' THEN RouteSteps.Authenticate ELSE NULL END DESC,
    CASE WHEN @Sort = 'StatusFlag desc' THEN RouteSteps.StatusFlag ELSE NULL END DESC,
    CASE WHEN @Sort = 'RouteName desc' THEN Routes.RouteName ELSE NULL END DESC,
    CASE WHEN @Sort = 'ProjectID desc' THEN Routes.ProjectID ELSE NULL END DESC,
    CASE WHEN @Sort = 'UserName desc' THEN Users.UserName ELSE NULL END DESC,
    CASE WHEN @Sort = 'ByUserName UserName desc' THEN U2.UserName ELSE NULL END DESC,
    CASE WHEN @Sort = 'FileID desc' THEN Files.FileID ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field1 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field1 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field1 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field1) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field1 desc' AND @fieldType = 3) THEN Convert(DateTime,Field1) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field3 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field3 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field3 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field3) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field3 desc' AND @fieldType = 3) THEN Convert(DateTime,Field3) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field4 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field4 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field4 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field4) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field4 desc' AND @fieldType = 3) THEN Convert(DateTime,Field4) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field5 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field5 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field5 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field5) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field5 desc' AND @fieldType = 3) THEN Convert(DateTime,Field5) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field6 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field6 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field6 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field6) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field6 desc' AND @fieldType = 3) THEN Convert(DateTime,Field6) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field7 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field7 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field7 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field7) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field7 desc' AND @fieldType = 3) THEN Convert(DateTime,Field7) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field8 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field8 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field8 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field8) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field8 desc' AND @fieldType = 3) THEN Convert(DateTime,Field8) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field9 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field9 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field9 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field9) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field9 desc' AND @fieldType = 3) THEN Convert(DateTime,Field9) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field10 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field10 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field10 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field10) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field10 desc' AND @fieldType = 3) THEN Convert(DateTime,Field10) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field11 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field11 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field11 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field11) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field11 desc' AND @fieldType = 3) THEN Convert(DateTime,Field11) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field12 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field12 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field12 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field12) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field12 desc' AND @fieldType = 3) THEN Convert(DateTime,Field12) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field13 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field13 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field13 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field13) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field13 desc' AND @fieldType = 3) THEN Convert(DateTime,Field13) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field14 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field14 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field14 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field14) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field14 desc' AND @fieldType = 3) THEN Convert(DateTime,Field14) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field15 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field15 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field15 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field15) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field15 desc' AND @fieldType = 3) THEN Convert(DateTime,Field15) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field16 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field16 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field16 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field16) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field16 desc' AND @fieldType = 3) THEN Convert(DateTime,Field16) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field17 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field17 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field17 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field17) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field17 desc' AND @fieldType = 3) THEN Convert(DateTime,Field17) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field18 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field18 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field18 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field18) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field18 desc' AND @fieldType = 3) THEN Convert(DateTime,Field18) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field19 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field19 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field19 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field19) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field19 desc' AND @fieldType = 3) THEN Convert(DateTime,Field19) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field20 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field20 ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field20 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field20) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'Field20 desc' AND @fieldType = 3) THEN Convert(DateTime,Field20) ELSE NULL END DESC,
    CASE WHEN (@Sort = 'RoutedItemID' OR @Sort = 'RoutedItemID asc') THEN DocumentRoute.DocumentRouteID ELSE NULL END,
    CASE WHEN (@Sort = 'RouteID' OR @Sort = 'RouteID asc') THEN Routes.RouteID ELSE NULL END,
    CASE WHEN (@Sort = 'RouteStepID' OR @Sort = 'RouteStepID asc') THEN DocumentRoute.RouteStepID ELSE NULL END,
    CASE WHEN (@Sort = 'CallingRoutedItemID' OR @Sort = 'CallingRoutedItemID asc') THEN DocumentRoute.CallingDocRouteID ELSE NULL END,
    CASE WHEN (@Sort = 'ParentRoutedItemID' OR @Sort = 'ParentRoutedItemID asc') THEN DocumentRoute.ParentDocRouteID ELSE NULL END,
    CASE WHEN (@Sort = 'SiblingRoutedItemID' OR @Sort = 'SiblingRoutedItemID asc') THEN DocumentRoute.SiblingDocRouteID ELSE NULL END,
    CASE WHEN (@Sort = 'RoutedObjectID' OR @Sort = 'RoutedObjectID asc') THEN DocumentRoute.DocumentID ELSE NULL END,
    CASE WHEN (@Sort = 'RoutedObjectType' OR @Sort = 'RoutedObjectType asc') THEN DocumentRoute.ItemType ELSE NULL END,
    CASE WHEN (@Sort = 'UserID' OR @Sort = 'UserID asc') THEN DocumentRoute.UserID ELSE NULL END,
    CASE WHEN (@Sort = 'ByUserID' OR @Sort = 'ByUserID asc') THEN U2.UserID ELSE NULL END,
    CASE WHEN (@Sort = 'Status' OR @Sort = 'Status asc') THEN DocumentRoute.Status ELSE NULL END,
    CASE WHEN (@Sort = 'FinalStep' OR @Sort = 'FinalStep asc') THEN DocumentRoute.Finished ELSE NULL END,
    CASE WHEN (@Sort = 'Completed' OR @Sort = 'Completed asc') THEN DocumentRoute.Completed ELSE NULL END,
    CASE WHEN (@Sort = 'StartDate' OR @Sort = 'StartDate asc') THEN DocumentRoute.StartDate ELSE NULL END,
    CASE WHEN (@Sort = 'DueDate' OR @Sort = 'DueDate asc') THEN DocumentRoute.DueDate ELSE NULL END,
    CASE WHEN (@Sort = 'OriginatorID' OR @Sort = 'OriginatorID asc') THEN DocumentRoute.OriginatorID ELSE NULL END,
    CASE WHEN (@Sort = 'RouteWait' OR @Sort = 'RouteWait asc') THEN DocumentRoute.RouteWait ELSE NULL END,
    CASE WHEN (@Sort = 'Direction' OR @Sort = 'Direction asc') THEN DocumentRoute.Direction ELSE NULL END,
    CASE WHEN (@Sort = 'ProcessFlag' OR @Sort = 'ProcessFlag asc') THEN DocumentRoute.ProcessFlag ELSE NULL END,
    CASE WHEN (@Sort = 'ProcessDate' OR @Sort = 'ProcessDate asc') THEN DocumentRoute.ProcessDate ELSE NULL END,
    CASE WHEN (@Sort = 'RelFileID' OR @Sort = 'RelFileID asc') THEN Documents.FileID ELSE NULL END,
    CASE WHEN (@Sort = 'Divider' OR @Sort = 'Divider asc') THEN Documents.DividerName ELSE NULL END,
    CASE WHEN (@Sort = 'Separator' OR @Sort = 'Separator asc') THEN Documents.Separator ELSE NULL END,
    CASE WHEN (@Sort = 'BatchDate' OR @Sort = 'BatchDate asc') THEN Documents.BatchDate ELSE NULL END,
    CASE WHEN (@Sort = 'Extension' OR @Sort = 'Extension asc') THEN Documents.Extension ELSE NULL END,
    CASE WHEN (@Sort = 'StepNumber' OR @Sort = 'StepNumber asc') THEN RouteSteps.StepNumber ELSE NULL END,
    CASE WHEN (@Sort = 'StepName' OR @Sort = 'StepName asc') THEN RouteSteps.StepName ELSE NULL END,
    CASE WHEN (@Sort = 'PromptComplete' OR @Sort = 'PromptComplete asc') THEN RouteSteps.PromptComplete ELSE NULL END,
    CASE WHEN (@Sort = 'PromptReject' OR @Sort = 'PromptReject asc') THEN RouteSteps.PromptReject ELSE NULL END,
    CASE WHEN (@Sort = 'PromptUser' OR @Sort = 'PromptUser asc') THEN RouteSteps.PromptUser ELSE NULL END,
    CASE WHEN (@Sort = 'ShowButtonComplete' OR @Sort = 'ShowButtonComplete asc') THEN RouteSteps.ShowButtonComplete ELSE NULL END,
    CASE WHEN (@Sort = 'ShowButtonReject' OR @Sort = 'ShowButtonReject asc') THEN RouteSteps.ShowButtonReject ELSE NULL END,
    CASE WHEN (@Sort = 'ShowButtonReassign' OR @Sort = 'ShowButtonReassign asc') THEN RouteSteps.ShowButtonReassign ELSE NULL END,
    CASE WHEN (@Sort = 'Authenticate' OR @Sort = 'Authenticate asc') THEN RouteSteps.Authenticate ELSE NULL END,
    CASE WHEN (@Sort = 'StatusFlag' OR @Sort = 'StatusFlag asc') THEN RouteSteps.StatusFlag ELSE NULL END,
    CASE WHEN (@Sort = 'RouteName' OR @Sort = 'RouteName asc') THEN Routes.RouteName ELSE NULL END,
    CASE WHEN (@Sort = 'ProjectID' OR @Sort = 'ProjectID asc') THEN Routes.ProjectID ELSE NULL END,
    CASE WHEN (@Sort = 'UserName' OR @Sort = 'UserName asc') THEN Users.UserName ELSE NULL END,
    CASE WHEN (@Sort = 'ByUserName' OR @Sort = 'ByUserName asc') THEN U2.UserName ELSE NULL END,
    CASE WHEN (@Sort = 'FileID' OR @Sort = 'FileID asc') THEN Files.FileID ELSE NULL END,
    CASE WHEN ((@Sort = 'Field1' OR @Sort = 'Field1 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field1 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field1' OR @Sort = 'Field1 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field1) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field1' OR @Sort = 'Field1 asc') AND @fieldType = 3) THEN Convert(DateTime,Field1) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field2' OR @Sort = 'Field2 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field2 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field2' OR @Sort = 'Field2 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field2) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field2' OR @Sort = 'Field2 asc') AND @fieldType = 3) THEN Convert(DateTime,Field2) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field3' OR @Sort = 'Field3 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field3 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field3' OR @Sort = 'Field3 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field3) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field3' OR @Sort = 'Field3 asc') AND @fieldType = 3) THEN Convert(DateTime,Field3) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field4' OR @Sort = 'Field4 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field4 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field4' OR @Sort = 'Field4 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field4) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field4' OR @Sort = 'Field4 asc') AND @fieldType = 3) THEN Convert(DateTime,Field4) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field5' OR @Sort = 'Field5 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field5 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field5' OR @Sort = 'Field5 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field5) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field5' OR @Sort = 'Field5 asc') AND @fieldType = 3) THEN Convert(DateTime,Field5) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field6' OR @Sort = 'Field6 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field6 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field6' OR @Sort = 'Field6 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field6) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field6' OR @Sort = 'Field6 asc') AND @fieldType = 3) THEN Convert(DateTime,Field6) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field7' OR @Sort = 'Field7 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field7 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field7' OR @Sort = 'Field7 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field7) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field7' OR @Sort = 'Field7 asc') AND @fieldType = 3) THEN Convert(DateTime,Field7) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field8' OR @Sort = 'Field8 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field8 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field8' OR @Sort = 'Field8 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field8) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field8' OR @Sort = 'Field8 asc') AND @fieldType = 3) THEN Convert(DateTime,Field8) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field9' OR @Sort = 'Field9 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field9 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field9' OR @Sort = 'Field9 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field9) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field9' OR @Sort = 'Field9 asc') AND @fieldType = 3) THEN Convert(DateTime,Field9) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field10' OR @Sort = 'Field10 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field10 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field10' OR @Sort = 'Field10 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field10) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field10' OR @Sort = 'Field10 asc') AND @fieldType = 3) THEN Convert(DateTime,Field10) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field11' OR @Sort = 'Field11 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field11 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field11' OR @Sort = 'Field11 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field11) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field11' OR @Sort = 'Field11 asc') AND @fieldType = 3) THEN Convert(DateTime,Field11) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field12' OR @Sort = 'Field12 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field12 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field12' OR @Sort = 'Field12 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field12) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field12' OR @Sort = 'Field12 asc') AND @fieldType = 3) THEN Convert(DateTime,Field12) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field13' OR @Sort = 'Field13 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field13 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field13' OR @Sort = 'Field13 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field13) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field13' OR @Sort = 'Field13 asc') AND @fieldType = 3) THEN Convert(DateTime,Field13) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field14' OR @Sort = 'Field14 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field14 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field14' OR @Sort = 'Field14 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field14) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field14' OR @Sort = 'Field14 asc') AND @fieldType = 3) THEN Convert(DateTime,Field14) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field15' OR @Sort = 'Field15 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field15 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field15' OR @Sort = 'Field15 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field15) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field15' OR @Sort = 'Field15 asc') AND @fieldType = 3) THEN Convert(DateTime,Field15) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field16' OR @Sort = 'Field16 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field16 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field16' OR @Sort = 'Field16 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field16) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field16' OR @Sort = 'Field16 asc') AND @fieldType = 3) THEN Convert(DateTime,Field16) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field17' OR @Sort = 'Field17 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field17 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field17' OR @Sort = 'Field17 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field17) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field17' OR @Sort = 'Field17 asc') AND @fieldType = 3) THEN Convert(DateTime,Field17) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field18' OR @Sort = 'Field18 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field18 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field18' OR @Sort = 'Field18 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field18) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field18' OR @Sort = 'Field18 asc') AND @fieldType = 3) THEN Convert(DateTime,Field18) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field19' OR @Sort = 'Field19 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field19 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field19' OR @Sort = 'Field19 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field19) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field19' OR @Sort = 'Field19 asc') AND @fieldType = 3) THEN Convert(DateTime,Field19) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field20' OR @Sort = 'Field20 asc') AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field20 ELSE NULL END,
    CASE WHEN ((@Sort = 'Field20' OR @Sort = 'Field20 asc') AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field20) ELSE NULL END,
    CASE WHEN ((@Sort = 'Field20' OR @Sort = 'Field20 asc') AND @fieldType = 3) THEN Convert(DateTime,Field20) ELSE NULL END,
    CASE WHEN (@Sort = '') THEN DocumentRouteID ELSE NULL END
) As RowID,
COUNT(*) OVER() AS TotalCount,
DocumentRoute.DocumentRouteID AS RoutedItemID, Routes.RouteID, DocumentRoute.RouteStepID, 
DocumentRoute.CallingDocRouteID AS CallingRoutedItemID, DocumentRoute.ParentDocRouteID AS ParentRoutedItemID, 
DocumentRoute.SiblingDocRouteID AS SiblingRoutedItemID, DocumentRoute.DocumentID AS RoutedObjectID, 
DocumentRoute.ItemType AS RoutedObjectType, DocumentRoute.UserID, DocumentRoute.ByUserID, DocumentRoute.Comment, 
DocumentRoute.Status, DocumentRoute.Finished AS FinalStep, DocumentRoute.Completed, DocumentRoute.StartDate, 
DocumentRoute.DueDate, DocumentRoute.OriginatorID, DocumentRoute.RouteWait, DocumentRoute.Direction, 
DocumentRoute.ProcessFlag, DocumentRoute.ProcessDate, Documents.FileID AS RelFileID, Documents.DividerName AS Divider,
Documents.Separator, Documents.BatchDate, Documents.Extension, RouteSteps.StepNumber, RouteSteps.StepName,  
RouteSteps.PromptComplete, RouteSteps.PromptReject, RouteSteps.PromptUser, RouteSteps.ShowButtonComplete,  
RouteSteps.ShowButtonReject, RouteSteps.ShowButtonReassign, RouteSteps.Authenticate, RouteSteps.StatusFlag, 
Routes.RouteName, Routes.ProjectID, Users.UserName, U2.UserName AS ByUserName,
Files.FileID, Field1, Field2,Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10,
Field11, Field12, Field13, Field14, Field15, Field16, Field17, Field18, Field19, Field20
FROM DocumentRoute WITH (NOLOCK)
INNER JOIN Documents WITH (NOLOCK) ON DocumentRoute.DocumentID = Documents.DocumentID
INNER JOIN Files WITH (NOLOCK) ON Files.FileID = Documents.FileID
LEFT JOIN Users WITH (NOLOCK) ON DocumentRoute.UserID = Users.UserID
LEFT JOIN Users U2 WITH (NOLOCK) ON DocumentRoute.ByUserID = U2.UserID
LEFT JOIN RouteSteps WITH (NOLOCK) ON DocumentRoute.RouteStepID = RouteSteps.RouteStepID
LEFT JOIN Routes WITH (NOLOCK) ON Routes.RouteID = RouteSteps.RouteID
WHERE DocumentRoute.UserID = @UserID AND (@ItemType <> 6 OR (@ItemType = 6 AND Files.ProjectID = @ProjectID)) AND DocumentRoute.Status = @Status
AND
CASE @OverDue
    WHEN -999 THEN 1
    WHEN 0 THEN CASE WHEN DocumentRoute.DueDate = '1/1/1990' OR DocumentRoute.DueDate > GetDate() THEN 1 ELSE 0 END
    ELSE CASE WHEN DocumentRoute.DueDate <> '1/1/1990' AND DocumentRoute.DueDate <= GetDate() THEN 1 ELSE 0 END
END =1
AND
CASE @ObjectID 
    WHEN 0 THEN 1
    ELSE CASE WHEN DocumentRoute.DocumentID = @ObjectID THEN 1 ELSE 0 END
END =1
AND
CASE @ItemType 
    WHEN 0 THEN 1
    WHEN 6 THEN CASE WHEN DocumentRoute.ItemType = 6 and Documents.Status > 0 THEN 1 ELSE 0 END
    ELSE CASE WHEN DocumentRoute.ItemType = @ItemType THEN 1 ELSE 0 END
END =1
AND
CASE @Direction 
    WHEN -999 THEN 1
    ELSE CASE WHEN DocumentRoute.Direction = @Direction THEN 1 ELSE 0 END
END =1
AND
CASE @ParentRoutedItemID 
    WHEN 0 THEN 1
    ELSE CASE WHEN DocumentRoute.ParentDocRouteID = @ParentRoutedItemID THEN 1 ELSE 0 END
END =1
AND
CASE @CallingRoutedItemID 
    WHEN 0 THEN 1
    ELSE CASE WHEN DocumentRoute.CallingDocRouteID = @CallingRoutedItemID THEN 1 ELSE 0 END
END =1
AND
CASE @SiblingRoutedItemID 
    WHEN 0 THEN 1
    ELSE CASE WHEN DocumentRoute.SiblingDocRouteID = @SiblingRoutedItemID THEN 1 ELSE 0 END
END =1
AND
CASE @RouteID 
    WHEN -999 THEN 1
    WHEN 0 THEN CASE WHEN DocumentRoute.RouteStepID = -1 THEN 1 ELSE 0 END
    ELSE CASE WHEN Routes.RouteID = @RouteID THEN 1 ELSE 0 END
END =1
AND
CASE @RouteStepID 
    WHEN 0 THEN 1
    ELSE CASE WHEN RouteSteps.RouteStepID = @RouteStepID THEN 1 ELSE 0 END
END =1
AND
CASE 
    WHEN @RouteName = '' THEN 1
    WHEN CHARINDEX('%',@RouteName) > 0 THEN CASE WHEN Routes.RouteName LIKE @RouteName THEN 1 ELSE 0 END
    ELSE CASE WHEN Routes.RouteName = @RouteName THEN 1 ELSE 0 END
END =1
AND
CASE 
    WHEN @RouteStepName = '' THEN 1
    WHEN CHARINDEX('%',@RouteStepName) > 0 THEN CASE WHEN RouteSteps.StepName LIKE @RouteStepName THEN 1 ELSE 0 END
    ELSE CASE WHEN RouteSteps.StepName = @RouteStepName THEN 1 ELSE 0 END
END =1;

How on this (or any other hover box) can I interrupt this into an index suggestion. The query is rather long and has several joins and case statements. Every 'How to read explain plan' guides I find talk about cost etc, but not how to read for indexes/stats that could improve if the SSMS doesn't suggest it.

Is there a good guide online that breaks down the 'how to follow paths for indexes/stats'?

I notice in your screenshot that the actual vs estimated rows are out by a factor of 50. If estimated was correct it might choose a different join operator. Have you tried updating statistics? Understanding query plans is a long but interesting road. I suggest you do some googling on actual vs estimated and statistics and see if that shines any light. — Nick.Mc, Oct 07 '15 at 22:57
@Nick.McDermaid is correct, try updating the statistic. My comment is however that not all problems can be fixed by adding indexes. It could be slow due to other design problems. If that is the case then you will never find what **your** looking for in the plan. A screen shot of the execution plan would be very helpful. — Paul Spain, Oct 08 '15 at 00:00
Yes the plan might be optimal, you just have a lot of data and an under resourced server. Certainly actual vs estimated is a good start though. — Nick.Mc, Oct 08 '15 at 00:17
I do realize an index might not be the solution, but was hoping there was a guide to translate the info into better understanding what might be needed to improve. I will start reading about actual vs. estimated, thanks for that info. I will add the image of the full plan to the original post. — Toolman21, Oct 08 '15 at 14:19
I think I added the new image to the original post, however imgur is blocked here so I can't verify. Thanks again. — Toolman21, Oct 08 '15 at 14:20
Could you post your query perhaps? Looking at execution plan looks like you're querying your DocumentRoute table twice (or SQL Server does an index merge there) and it has to scan the whole index. On top of that - I see [Key Lookup](https://technet.microsoft.com/en-us/library/bb326635%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396), it indicates that some fields are missing in your index, please look at what's being done in it and perhaps add index on that field(the one in Key Lookup operator) — Evaldas Buinauskas, Oct 08 '15 at 14:27
I checked and the stats are updated a couple of hours ago and I still receive the same results. — Toolman21, Oct 08 '15 at 14:28
I added the code I did have to remove this from the query to make it fit the character limit: ` CASE WHEN (@Sort = 'Field2 desc' AND @fieldType <> 2 AND @fieldType <> 3 AND @fieldType <> 5) THEN Field2 ELSE NULL END DESC, CASE WHEN (@Sort = 'Field2 desc' AND (@fieldType = 2 OR @fieldType = 5)) THEN Convert(DECIMAL,Field2) ELSE NULL END DESC, CASE WHEN (@Sort = 'Field2 desc' AND @fieldType = 3) THEN Convert(DateTime,Field2) ELSE NULL END DESC, ` It went between the 1 & 3 above. — Toolman21, Oct 08 '15 at 14:32
Also it is worth noting this is from a 'supported' application. It just just performing really slow and the company isn't much help at this point. We are trying to do what we can to speed things up. I can't change the query, but I can modify the DB to try to compensate. — Toolman21, Oct 08 '15 at 14:35
I removed last paragraph to fit query in question as it didn't contain a lot of information. You can reject this edit. — Evaldas Buinauskas, Oct 08 '15 at 14:44

score 0 · Accepted Answer · answered Oct 08 '15 at 14:47

0

I think that performance hit is exactly in here(who could've thought that fat arrows could show that):

I guess that column, that's being retrieved from Key Lookup operator could be a useful candidate key for index in DocumentRoute table.

answered Oct 08 '15 at 14:47

Evaldas Buinauskas

13,739
11
55
107

I know that has the larger part of the processing, but it already is using a clustered key for lookup. (added image to original post) This might be where I need the assistance in reading the explain plan to determine more of what is going on. If it is already using an index, is there room left for improvement? – Toolman21 Oct 08 '15 at 15:09
Key lookup will always look at clustered index. Generally. Key lookups are bad and make a huge impact in performance. See that two first queries produce 174k records. After doing a key lookup it amount of records gets reduced to 14. Look what column is being used there. It might give an idea what index you need. – Evaldas Buinauskas Oct 08 '15 at 15:12
Adding another image to the post that shows more expanded information. IF I am understanding correctly I need to create a covering index for the key lookup. Which would be for DocumentRouteID and include the outputs that are listed in the image. At what point do you put them IN the index vs INCLUDED in the index? Do you just use the top three in the output list with the key or is there some rule of thumb? Thank you very much for you help. – Toolman21 Oct 08 '15 at 15:32
You put them IN index when you want them to be seeked and actually indexed. When you put them in INCLUDED, they will not be indexed, but will be inside of index(it lets you avoid lookups). What columns does your index DocumentRoute_UserID contain? – Evaldas Buinauskas Oct 08 '15 at 16:31
DocumentRoute_UserID just contains the UserID column. The clustered index it uses DocumentRoute_DocumentRouteID_cl1 just contains the DocumentRouteID column. The column byUserID does not have an index. – Toolman21 Oct 08 '15 at 17:04
Could you join this room for a chat? https://chat.stackoverflow.com/rooms/91776/evaldas-index – Evaldas Buinauskas Oct 08 '15 at 17:09
Apparently l don't have enough reputation to chat. – Toolman21 Oct 08 '15 at 17:17

Guidance in reading SQL Server Explain plan

1 Answers1