sql-server - SQL查询比存储过程运行得更快

Question

我最近在生产服务器上修改了一个存储过程，它开始表现不佳，但当我独立运行查询时，它运行良好。时间上的差异很大，即比较 5 秒和 10 分钟是令人震惊的。

我读了这篇文章SQL Server: Query fast, but slow from procedure并尝试用所有可能的解决方案解决我的问题，但存储过程仍然无法工作，它在时间上有相同的差异。

任何人都可以让我知道我应该更改什么，以便我的存储过程至少与查询运行得一样快吗？我对 SQL Server 的这种行为感到有点惊讶

好计划

  |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Assessment].[PK_Assessment] AS [a]), SEEK:([a].[Assessment_Id]=[@AssessmentID]) ORDERED FORWARD)
  |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(float(53),[Expr1008]-(1),0)))
       |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1012],0)))
            |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
                 |--Stream Aggregate(GROUP BY:([ci].[Candidate_Instance_Id]))
                      |--Parallelism(Gather Streams, ORDER BY:([rr].[Candidate_Instance_ID] ASC))
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
                                |--Sort(ORDER BY:([rr].[Candidate_Instance_ID] ASC))
                                |    |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]))
                                |--Stream Aggregate(DEFINE:([ci].[Candidate_Instance_Id]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Candidate_Instance_Id] as [ci].[Candidate_Instance_Id])))
                                     |--Nested Loops(Inner Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
                                          |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
                                          |--Table Scan(OBJECT:(@AssessmentStatuses))
  |--Table Insert(OBJECT:(@ItemDetailTable), SET:([Candidate_Id] = [IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id],[Instance_End_Time] = [IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time],[Candidate_Instance_Id] = [IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID],[Assessment_Id] = [IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID],[OverAllScore] = [Expr1030],[Section_ID] = [IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID],[AttempteCount] = [Expr1025],[CorrectCount] = [Expr1026],[Respone_Time] = [Expr1027],[Display_Name] = [Expr1032],[Display_Type_Id] = [IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id],[MaxMarks] = [Expr1033],[Item_ID] = [IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID],[Percentile] = NULL))
       |--Compute Scalar(DEFINE:([Expr1033]=CONVERT_IMPLICIT(int,[Expr1031],0)))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1032]=CONVERT_IMPLICIT(varchar(100),[IndusLynk].[dbo].[Display_Type].[Display_Name] as [dt].[Display_Name],0)))
                      |--Sort(DISTINCT ORDER BY:([ci].[Instance_End_Time] DESC, [rr].[Candidate_Instance_ID] DESC, [c].[Candidate_Id] ASC, [rr].[Section_ID] ASC, [q].[Display_Type_Id] ASC, [Expr1030] ASC, [Expr1025] ASC, [Expr1026] ASC, [Expr1027] ASC, [dt].[Display_Name] ASC, [Expr1031] ASC, [rr].[Item_ID] ASC))
                           |--Compute Scalar(DEFINE:([Expr1030]=round([Expr1024],(2)), [Expr1031]=CASE WHEN [IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id]=(8) THEN [Expr1028] ELSE [Expr1029] END))
                                |--Stream Aggregate(GROUP BY:([rr].[Candidate_Instance_ID], [c].[First_Name], [c].[Last_Name], [rr].[Section_ID], [q].[Display_Type_Id], [rr].[Item_ID]) DEFINE:([Expr1024]=SUM([Expr1034]), [Expr1025]=SUM([Expr1035]), [Expr1026]=SUM([Expr1036]), [Expr1027]=SUM([Expr1037]), [Expr1028]=SUM([Expr1038]), [Expr1029]=MAX([Expr1039]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [dt].[Display_Name]=ANY([IndusLynk].[dbo].[Display_Type].[Display_Name] as [dt].[Display_Name])))
                                     |--Compute Scalar(DEFINE:([Expr1038]=CASE WHEN isnull([IndusLynk].[dbo].[Answer].[Answer_Weightage] as [ans].[Answer_Weightage],(0.000000000000000e+000))>(-1.000000000000000e+000) THEN isnull([IndusLynk].[dbo].[Answer].[Answer_Weightage] as [ans].[Answer_Weightage],(0.000000000000000e+000)) ELSE (0.000000000000000e+000) END, [Expr1039]=isnull([IndusLynk].[dbo].[Answer].[Answer_Weightage] as [ans].[Answer_Weightage],(0.000000000000000e+000))))
                                          |--Nested Loops(Left Outer Join, OUTER REFERENCES:([id].[Answer_Id], [Expr1052]) WITH ORDERED PREFETCH)
                                               |--Nested Loops(Left Outer Join, OUTER REFERENCES:([rr].[Item_ID]))
                                               |    |--Sort(ORDER BY:([rr].[Candidate_Instance_ID] ASC, [c].[First_Name] ASC, [c].[Last_Name] ASC, [rr].[Section_ID] ASC, [q].[Display_Type_Id] ASC, [rr].[Item_ID] ASC))
                                               |    |    |--Stream Aggregate(GROUP BY:([rr].[Processed_Response_ID]) DEFINE:([rr].[Candidate_Instance_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [rr].[Section_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID]), [rr].[Item_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [c].[First_Name]=ANY([IndusLynk].[dbo].[Candidate].[First_Name] as [c].[First_Name]), [c].[Last_Name]=ANY([IndusLynk].[dbo].[Candidate].[Last_Name] as [c].[Last_Name]), [q].[Display_Type_Id]=ANY([IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id]), [dt].[Display_Type_Id]=ANY([IndusLynk].[dbo].[Display_Type].[Display_Type_Id] as [dt].[Display_Type_Id]), [dt].[Display_Name]=ANY([IndusLynk].[dbo].[Display_Type].[Display_Name] as [dt].[Display_Name]), [Expr1034]=ANY([Expr1034]), [Expr1035]=ANY([Expr1035]), [Expr1036]=ANY([Expr1036]), [Expr1037]=ANY([Expr1037])))
                                               |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ci].[Candidate_Id]))
                                               |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([q].[Display_Type_Id]))
                                               |    |              |    |--Nested Loops(Inner Join, OUTER REFERENCES:([i].[Question_Id]))
                                               |    |              |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Item_ID]))
                                               |    |              |    |    |    |--Nested Loops(Inner Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
                                               |    |              |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
                                               |    |              |    |    |    |    |    |--Compute Scalar(DEFINE:([Expr1034]=isnull([IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained],(0.000000000000000e+000)), [Expr1035]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Display_Type_ID] as [rr].[Display_Type_ID]=(2) THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted] IS NULL THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time]>(0.000000000000000e+000) THEN (1) ELSE (0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END, [Expr1036]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained]>(0.000000000000000e+000) THEN (1) ELSE (0) END, [Expr1037]=isnull([IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time],(0.000000000000000e+000))))
                                               |    |              |    |    |    |    |    |    |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]),  WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]) ORDERED FORWARD)
                                               |    |              |    |    |    |    |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
                                               |    |              |    |    |    |    |--Table Scan(OBJECT:(@AssessmentStatuses))
                                               |    |              |    |    |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Item].[PK_Item] AS [i]), SEEK:([i].[Item_Id]=[IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]) ORDERED FORWARD)
                                               |    |              |    |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Question].[PK_Question] AS [q]), SEEK:([q].[Question_Id]=[IndusLynk].[dbo].[Item].[Question_Id] as [i].[Question_Id]) ORDERED FORWARD)
                                               |    |              |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Display_Type].[PK_Display_Type] AS [dt]), SEEK:([dt].[Display_Type_Id]=[IndusLynk].[dbo].[Question].[Display_Type_Id] as [q].[Display_Type_Id]) ORDERED FORWARD)
                                               |    |              |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate].[PK_Candidate] AS [c]), SEEK:([c].[Candidate_Id]=[IndusLynk].[dbo].[Candidate_Instance].[Candidate_Id] as [ci].[Candidate_Id]) ORDERED FORWARD)
                                               |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Item_Details].[PK_Item_Details] AS [id]), SEEK:([id].[Item_Id]=[IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]) ORDERED FORWARD)
                                               |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Answer].[PK_Answer] AS [ans]), SEEK:([ans].[Answer_Id]=[IndusLynk].[dbo].[Item_Details].[Answer_Id] as [id].[Answer_Id]) ORDERED FORWARD)
  |--Table Insert(OBJECT:(@SectionDetailTable), SET:([Candidate_Id] = [IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id],[Candidate_Instance_Id] = [IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID],[Assessment_Id] = [IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID],[OverAllScore] = [Expr1025],[Section_ID] = [IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID],[Section_Name] = [Expr1028],[Section_Description] = [Expr1029],[AttempteCount] = [Expr1020],[CorrectCount] = [Expr1021],[Respone_Time] = [Expr1026],[TotaQuestionCount] = [Expr1023],[MaxMarks] = [Expr1027],[Percentile] = NULL))
       |--Compute Scalar(DEFINE:([Expr1025]=round([Expr1019],(2)), [Expr1026]=CASE WHEN [IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration]<>(0) THEN CASE WHEN [Expr1022]/(6.000000000000000e+001)>CONVERT_IMPLICIT(float(53),[IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration],0) THEN CONVERT_IMPLICIT(float(53),[IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration],0) ELSE [Expr1022]/(6.000000000000000e+001) END ELSE [Expr1022]/(6.000000000000000e+001) END, [Expr1027]=CONVERT(float(53),[Expr1024],0)))
            |--Top(ROWCOUNT est 0)
                 |--Sort(ORDER BY:([ci].[Instance_End_Time] DESC, [rr].[Candidate_Instance_ID] DESC, [c].[Candidate_Id] ASC, [rr].[Section_ID] ASC))
                      |--Compute Scalar(DEFINE:([Expr1028]=CONVERT_IMPLICIT(varchar(100),[IndusLynk].[dbo].[Section].[Section_Title] as [s].[Section_Title],0), [Expr1029]=CONVERT_IMPLICIT(varchar(100),[IndusLynk].[dbo].[Section].[Section_Description] as [s].[Section_Description],0)))
                           |--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(int,[Expr1047],0), [Expr1024]=CASE WHEN [Expr1048]=(0) THEN NULL ELSE [Expr1049] END))
                                |--Stream Aggregate(GROUP BY:([rr].[Candidate_Instance_ID], [c].[First_Name], [c].[Last_Name], [rr].[Section_ID]) DEFINE:([Expr1047]=Count(*), [Expr1019]=SUM([Expr1030]), [Expr1020]=SUM([Expr1031]), [Expr1021]=SUM([Expr1032]), [Expr1022]=SUM([Expr1033]), [Expr1048]=COUNT_BIG(@ItemDetailTable.[MaxMarks] as [id].[MaxMarks]), [Expr1049]=SUM(@ItemDetailTable.[MaxMarks] as [id].[MaxMarks]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [s].[Section_Title]=ANY([IndusLynk].[dbo].[Section].[Section_Title] as [s].[Section_Title]), [s].[Section_Description]=ANY([IndusLynk].[dbo].[Section].[Section_Description] as [s].[Section_Description]), [s].[Section_Duration]=ANY([IndusLynk].[dbo].[Section].[Section_Duration] as [s].[Section_Duration])))
                                     |--Nested Loops(Inner Join, WHERE:([IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]=@ItemDetailTable.[Candidate_Instance_Id] as [id].[Candidate_Instance_Id] AND [IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID]=@ItemDetailTable.[Section_ID] as [id].[Section_ID] AND @ItemDetailTable.[Item_ID] as [id].[Item_ID]=[IndusLynk].[dbo].[Processed_Response].[Item_ID] as [rr].[Item_ID]))
                                          |--Sort(ORDER BY:([rr].[Candidate_Instance_ID] ASC, [c].[First_Name] ASC, [c].[Last_Name] ASC, [rr].[Section_ID] ASC))
                                          |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ci].[Candidate_Id]))
                                          |         |--Nested Loops(Left Semi Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
                                          |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
                                          |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[Section_Id]))
                                          |         |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Section_ID]))
                                          |         |    |    |    |    |--Compute Scalar(DEFINE:([Expr1030]=isnull([IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained],(0.000000000000000e+000)), [Expr1031]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Display_Type_ID] as [rr].[Display_Type_ID]=(2) THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted] IS NULL THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time]>(0.000000000000000e+000) THEN (1) ELSE (0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END, [Expr1032]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained]>(0.000000000000000e+000) THEN (1) ELSE (0) END, [Expr1033]=isnull([IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time],(0.000000000000000e+000))))
                                          |         |    |    |    |    |    |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]))
                                          |         |    |    |    |    |--Index Seek(OBJECT:([IndusLynk].[dbo].[Section].[IX_Section] AS [s]), SEEK:([s].[Assessment_Id]=[@AssessmentID] AND [s].[Section_Id]=[IndusLynk].[dbo].[Processed_Response].[Section_ID] as [rr].[Section_ID]) ORDERED FORWARD)
                                          |         |    |    |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Section].[PK_Section] AS [s]), SEEK:([s].[Section_Id]=[IndusLynk].[dbo].[Section].[Section_Id] as [s].[Section_Id]) LOOKUP ORDERED FORWARD)
                                          |         |    |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
                                          |         |    |--Table Scan(OBJECT:(@AssessmentStatuses))
                                          |         |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate].[PK_Candidate] AS [c]), SEEK:([c].[Candidate_Id]=[IndusLynk].[dbo].[Candidate_Instance].[Candidate_Id] as [ci].[Candidate_Id]) ORDERED FORWARD)
                                          |--Table Scan(OBJECT:(@ItemDetailTable AS [id]))
  |--Table Insert(OBJECT:(@AssessmentDetailTable), SET:([Candidate_Id] = [IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id],[Candidate_Instance_Id] = [IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID],[Assessment_Id] = [IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID],[First_Name] = [IndusLynk].[dbo].[Candidate].[First_Name] as [c].[First_Name],[Last_Name] = [Expr1018],[OverAllScore] = [Expr1019],[Instance_Start_Time] = [Expr1020],[AttempteCount] = [Expr1015],[CorrectCount] = [Expr1016],[Respone_Time] = [Expr1021],[InvitationID] = [IndusLynk].[dbo].[Candidate_Instance].[Invitation_Id] as [ci].[Invitation_Id],[Email_ID] = [Expr1023],[RowNumber] = [Expr1024],[Percentile] = NULL))
       |--Compute Scalar(DEFINE:([Expr1024]=CONVERT_IMPLICIT(int,[Expr1022],0)))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(varchar(50),[IndusLynk].[dbo].[Candidate].[Email_Id] as [c].[Email_Id],0)))
                      |--Sequence Project(DEFINE:([Expr1022]=row_number))
                           |--Segment
                                |--Sort(ORDER BY:([ci].[Instance_End_Time] DESC, [rr].[Candidate_Instance_ID] DESC, [c].[Candidate_Id] ASC))
                                     |--Compute Scalar(DEFINE:([Expr1018]=isnull([IndusLynk].[dbo].[Candidate].[Last_Name] as [c].[Last_Name],''), [Expr1019]=round([Expr1014],(2)), [Expr1020]=isnull([IndusLynk].[dbo].[Candidate_Instance].[Instance_Start_Time] as [ci].[Instance_Start_Time],dateadd(minute, -[@AssessmentDuration],[IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time])), [Expr1021]=CASE WHEN [Expr1017]/(6.000000000000000e+001)>CONVERT_IMPLICIT(float(53),[@AssessmentDuration],0) THEN CONVERT_IMPLICIT(float(53),[@AssessmentDuration],0) ELSE [Expr1017]/(6.000000000000000e+001) END))
                                          |--Stream Aggregate(GROUP BY:([c].[Email_Id], [rr].[Candidate_Instance_ID]) DEFINE:([Expr1014]=SUM([Expr1026]), [Expr1015]=SUM([Expr1027]), [Expr1016]=SUM([Expr1028]), [Expr1017]=SUM([Expr1029]), [ci].[Instance_End_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_End_Time] as [ci].[Instance_End_Time]), [c].[Candidate_Id]=ANY([IndusLynk].[dbo].[Candidate].[Candidate_Id] as [c].[Candidate_Id]), [rr].[Assessment_ID]=ANY([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]), [c].[First_Name]=ANY([IndusLynk].[dbo].[Candidate].[First_Name] as [c].[First_Name]), [c].[Last_Name]=ANY([IndusLynk].[dbo].[Candidate].[Last_Name] as [c].[Last_Name]), [ci].[Instance_Start_Time]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Instance_Start_Time] as [ci].[Instance_Start_Time]), [ci].[Invitation_Id]=ANY([IndusLynk].[dbo].[Candidate_Instance].[Invitation_Id] as [ci].[Invitation_Id])))
                                               |--Sort(ORDER BY:([c].[Email_Id] ASC, [rr].[Candidate_Instance_ID] ASC))
                                                    |--Nested Loops(Inner Join, OUTER REFERENCES:([ci].[Candidate_Id]))
                                                         |--Nested Loops(Left Semi Join, WHERE:([IndusLynk].[dbo].[Candidate_Instance].[Instance_Status] as [ci].[Instance_Status]=[status]))
                                                         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rr].[Candidate_Instance_ID]))
                                                         |    |    |--Compute Scalar(DEFINE:([Expr1026]=isnull([IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained],(0.000000000000000e+000)), [Expr1027]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Display_Type_ID] as [rr].[Display_Type_ID]=(2) THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted] IS NULL THEN CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time]>(0.000000000000000e+000) THEN (1) ELSE (0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END ELSE CONVERT_IMPLICIT(int,isnull([IndusLynk].[dbo].[Processed_Response].[IsAttempted] as [rr].[IsAttempted],(0)),0) END, [Expr1028]=CASE WHEN [IndusLynk].[dbo].[Processed_Response].[Marks_Obtained] as [rr].[Marks_Obtained]>(0.000000000000000e+000) THEN (1) ELSE (0) END, [Expr1029]=isnull([IndusLynk].[dbo].[Processed_Response].[Response_Time] as [rr].[Response_Time],(0.000000000000000e+000))))
                                                         |    |    |    |--Clustered Index Scan(OBJECT:([IndusLynk].[dbo].[Processed_Response].[PK_Processed_Response] AS [rr]), WHERE:([IndusLynk].[dbo].[Processed_Response].[Assessment_ID] as [rr].[Assessment_ID]=[@AssessmentID] AND [IndusLynk].[dbo].[Processed_Response].[Client_ID] as [rr].[Client_ID]=[@UserID]))
                                                         |    |    |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate_Instance].[PK_Candidate_Instance] AS [ci]), SEEK:([ci].[Candidate_Instance_Id]=[IndusLynk].[dbo].[Processed_Response].[Candidate_Instance_ID] as [rr].[Candidate_Instance_ID]) ORDERED FORWARD)
                                                         |    |--Table Scan(OBJECT:(@AssessmentStatuses))
                                                         |--Clustered Index Seek(OBJECT:([IndusLynk].[dbo].[Candidate].[PK_Candidate] AS [c]), SEEK:([c].[Candidate_Id]=[IndusLynk].[dbo].[Candidate_Instance].[Candidate_Id] as [ci].[Candidate_Id]) ORDERED FORWARD)
  |--Table Insert(OBJECT:(@AssessmentFilteredTable), SET:([Candidate_Id] = [Candidate_Id],[Candidate_Instance_Id] = [Candidate_Instance_Id],[Assessment_Id] = [Assessment_Id],[First_Name] = [First_Name],[Last_Name] = [Last_Name],[OverAllScore] = [OverAllScore],[Instance_Start_Time] = [Instance_Start_Time],[AttempteCount] = [AttempteCount],[CorrectCount] = [CorrectCount],[Respone_Time] = [Respone_Time],[Percentile] = [Percentile],[RowNumber] = [Expr1009]))
       |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1008],0)))
            |--Top(ROWCOUNT est 0)
                 |--Sequence Project(DEFINE:([Expr1008]=row_number))
                      |--Segment
                           |--Sort(ORDER BY:([RowNumber] ASC))

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN @SectionDetailTable.[OverAllScore] as [i].[OverAllScore]>(0.000000000000000e+000) THEN isnull(@SectionDetailTable.[Percentile] as [i].[Percentile],(1.000000000000000e+002)) ELSE isnull(@SectionDetailTable.[Percentile] as [i].[Percentile],(0.000000000000000e+000)) END))
       |--Table Scan(OBJECT:(@SectionDetailTable AS [i]))
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [OverAllScore]>(0.000000000000000e+000) THEN isnull([Percentile],(1.000000000000000e+002)) ELSE isnull([Percentile],(0.000000000000000e+000)) END))
       |--Table Scan(OBJECT:(@AssessmentDetailTable))
  |--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]))
       |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1009],0)))
            |--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
                 |--Table Scan(OBJECT:(@AssessmentDetailTable))

Answer 1

SP 是预编译的，可能使用了“错误”的查询计划，这对于传递到 SP 的参数不利。您可以在 SP 内的查询上添加OPTION (RECOMPILE)，以强制 SQL Server 通过查询优化器重新运行查询，并找到给定参数的最佳查询。

参见here或here有关一些示例和背景信息...