3

I have a stored procedure which I modified recently on my production server and it started performing poorly but when I run the query independently it runs fine. The difference in timing is a lot i.e comparing 5 secs to 10 mins is alarming.

I read this post SQL Server: Query fast, but slow from procedure and tried resolving my issue with all the possible solutions but still the stored proc does not work it is having the same difference in timings.

Can anyone let me know what should I change so that my stored proc runs at least as fast as the query? I am a bit surprised by this behavior of SQL Server

Good Plan 

  |--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))
Community
  • 1
  • 1
Nikshep
  • 2,117
  • 4
  • 21
  • 30

1 Answers1

7

SPs are precompiled, it may be that a "bad" query plan is used which is not good for the arguments passed into the SP. You can add a OPTION (RECOMPILE) on the queries inside the SP to force SQL Server to re-run the query through the query optimizer and find the best query for the given parameters.

See here or here for some examples and background info...

Lucero
  • 59,176
  • 9
  • 122
  • 152
  • I don't have many if else statments in the sp and it is a simple query just that is fetches a lot of aggregated data. I did try to recompile the stored procedure but still it is failing. – Nikshep Jul 25 '11 at 11:04
  • 2
    Please read the answer carefully: I'm not talking about recompiling the SP, and it has nothing at all to do with if/else. From the plan you posted I gather that the `INSERT` statement in the SP does some faily complex processing for getting the data to be inserted, maybe this is the statement needs to be suffixed with the `OPTION (RECOMPILE)` query option (or maybe another one). If you run the SP with execution plan, you'll quickly be able to see which statement in the SP is the one slowing your query down, apply the option to that one. – Lucero Jul 25 '11 at 11:16
  • Christ allmighty, I was just about to make a new database and re-script everything into it. I had a query on our test system which usually runs at 5 to 10 minutes "suddenly" run 12 hours after restoring the surrounding databases from Live. Added this option and it's back to 11 minutes (Live has more data than test, so that's cool). THANK YOU! – Vincent Vancalbergh Apr 28 '14 at 20:23