none
How to understand SQL Server 2008 Execution Plan *sort*?

    Question

  • I am using SQL Server 2008 and am I displayed teh Execution Plan.  However, the major bottleneck it is identifying is not part of my query.  Why would this happen? 

    Also, I only have one SORT, which I commented out prior to running this.  So I am assuming this SORT which uses the most resources (72%) is an IMPLICIT sort. 

    I also commented out all instances of "Actual_Date" from my query, but it still identifies SORTING on this field.  I created and populated the two temp tables prior to running this query.  I am new to Execution Plan. 

    Here is my query:

    USE [evolv_reports]
    GO
    
    SET NOCOUNT ON
    
    declare @SupervisorID uniqueidentifier
    ,	@ClientID uniqueidentifier
    ,	@StaffID uniqueidentifier
    ,	@FacilityID       varchar(max)
    ,	@ProgramID uniqueidentifier
    ,	@ServiceDateStart smalldatetime
    ,	@ServiceDateEnd smalldatetime
    
    set @SupervisorID = Null
    set @ClientID = Null
    set @StaffID = Null
    set @FacilityID = Null
    set @ProgramID = Null
    set @ServiceDateStart = Null
    set @ServiceDateEnd = Null
    
    Select
    --Plan Information
    [#TEMP_EVENT_LOG].[event_log_id],
    [#TEMP_EVENT_LOG].[event_agency_id],
    [#TEMP_EVENT_LOG].[people_id],
    [service_plan_header].[service_plan_header_id],
    [program_info].[program_name],
    [#TEMP_EVENT_LOG].[staff_id],
    [people].[first_name],
    [people].[last_name],
    [people].[name_suffix],
    [staff].[title],
    [#TEMP_EVENT_LOG].[date_entered],
    [group_profile].[profile_name],
    [group_profile].[license_number],
    [service_track_current_view].[id_no],
    [client_personal_view].[full_name],
    --
    [#TEMP_EVENT_LOG].[belongs_to_event],
    [#TEMP_EVENT_LOG].[program_providing_service],
    [#TEMP_EVENT_LOG].[site_providing_service],
    [service_plan_header].[service_plan_setup_header_id],
    [service_plan_header].[service_plan_title],
    [service_plan_setup_header].[service_plan_name],
    [#TEMP_EVENT_LOG].[event_definition_id],
    [service_plan_header].[parent_service_plan_id],
    [service_plan_header].[child_service_plan_id],
    [#TEMP_EVENT_LOG].[is_initial],
    [service_plan_header].[is_reviewed],
    [service_plan_header].[service_plan_is_current],
    [service_plan_header].[total_amount_authorized],
    --
    --[#TEMP_EVENT_LOG].[actual_date],
    [#TEMP_EVENT_LOG].[end_date],
    [#TEMP_EVENT_LOG].[duration],
    [#TEMP_EVENT_LOG].[approval_sent_to],
    [service_plan_header_x].[Progress],
    [service_plan_header_x].[Struggles],
    [service_plan_header_x].[New_Information_Assessment],
    [service_plan_header_x].[Reprioritization_Drivers],
    [udl_pdt].[family_member_name] [PersonDischargedto],
    [udl_cs].[description] [Custody_Status],
    [service_plan_header_x].[Discharge_Location],
    [service_plan_header_x].[Discharge_Phone],
    [service_plan_header_x].[Caseworker_Name],
    [service_plan_header_x].[Caseworker_Phone],
    [service_plan_header_x].[PO_Name],
    [service_plan_header_x].[PO_Phone],
    
    --Vocational/Employment
    CASE WHEN 
    	[service_plan_header_x].[Vocational_setting] Is Null AND
    	[service_plan_header_x].[Vocational_Contact] Is Null AND
    	[service_plan_header_x].[Vocational_Address] Is Null AND
    	[service_plan_header_x].[Vocational_Phone] Is Null AND
    	[service_plan_header_x].[Place_of_employment] Is Null AND
    	[service_plan_header_x].[Position_Job_title] Is Null AND
    	[service_plan_header_x].[Employer_Address] Is Null AND
    	[service_plan_header_x].[Employer_phone] Is Null AND
    	[service_plan_header_x].[Length_of_Employment] Is Null
    THEN 1 ELSE 0 END AS [Vocational_Hidden],
    [service_plan_header_x].[Vocational_setting],
    [service_plan_header_x].[Vocational_Contact],
    [service_plan_header_x].[Vocational_Address],
    [service_plan_header_x].[Vocational_Phone],
    [service_plan_header_x].[Place_of_employment],
    [service_plan_header_x].[Position_Job_title],
    [service_plan_header_x].[Employer_Address],
    [service_plan_header_x].[Employer_phone],
    [udl_loe].[description] [Length_of_Employment],
    
    --  --Education
    CASE WHEN
    	[service_plan_header_x].[Post_Discharge_School_Setting] Is Null AND
    	[service_plan_header_x].[School_Address] Is Null AND
    	[service_plan_header_x].[School_Contact] Is Null AND
    	[service_plan_header_x].[School_Phone] Is Null AND
    	NULLIF([service_plan_header_x].[IEP_Needed],0) Is Null
    THEN 1 ELSE 0 END AS [Education_Hidden],
    [udl_pds].[profile_name] [Post_Discharge_School_Setting],
    [service_plan_header_x].[School_Address],
    [service_plan_header_x].[School_Contact],
    [service_plan_header_x].[School_Phone],
    [service_plan_header_x].[IEP_Needed],
    
    --  --Aftercare
    CASE WHEN
    	[service_plan_header_x].[UDF_Discharge_Educational_Comments] Is Null AND 
    	[service_plan_header_x].[AftercareServiceProvider]  Is Null AND 
    	[service_plan_header_x].[Aftercare_appointment_Date]  Is Null AND 
    	[service_plan_header_x].[Services_Needed] Is Null
    THEN 1 ELSE 0 END AS [Aftercare_Hidden],
    [service_plan_header_x].[UDF_Discharge_Educational_Comments],
    [udl_asp].[profile_name] [AftercareServiceProvider],
    [service_plan_header_x].[Aftercare_appointment_Date],
    [service_plan_header_x].[Services_Needed],
    
    --  --Medical Needs
    CASE WHEN
    	[service_plan_header_x].[Medical_Needs] Is Null AND
    	[service_plan_header_x].[Medical_Provider] Is Null AND
    	[service_plan_header_x].[Physician_Address] Is Null AND
    	[service_plan_header_x].[Physician_Phone] Is Null
    THEN 1 ELSE 0 END AS [Medical_Hidden],
    [service_plan_header_x].[Medical_Needs],
    [service_plan_header_x].[Medical_Provider],
    [service_plan_header_x].[Physician_Address],
    [service_plan_header_x].[Physician_Phone],
    
    --  --Discharge (Required)
    [service_plan_header_x].[Projected_StepDown_Date],
    [udl_tsp].[description] [Target_StepDown_Placement],
    [service_plan_header_x].[Projected_YVDischarge_Date],
    [service_plan_header_x].[UDF_NCS],
    [service_plan_header_x].[Comments],
    
    --  --Risk Assessment (for Safety Plan) --Added 1/5/11 - KMH
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Self_Harm_Risk]) AS Self_Harm_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[SuicidalBehavior]) AS SuicidalBehavior,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Other_Self_Harm_Risk])AS Other_Self_Harm_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Psychosis_Risk]) AS Psychosis_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Visual_Hallucination]) AS Visual_Hallucinations,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Danger_To_Others_Risk]) AS Danger_to_Others_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Runaway_Risk]) AS Runaway_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Fire_Setting_Risk]) AS Fire_Setting_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Sexual_Aggression_Risk]) AS Sexual_Aggression_Risk,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[inapp_sex_bx]) AS inapp_sex_bx,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[gang_involvement]) AS gang_involvement,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[caregiver_concerns]) AS caregiver_concerns,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Domestic_Violence]) AS Domestic_Violence,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Physical_aggression]) AS Physical_Aggression,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[AbuseNeglect]) AS AbuseNeglect,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[EnvironmentalRisks]) AS EnvironmentalRisks,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Hand_Guns]) AS Hand_Guns,
    [service_plan_header_x].[How_many] AS NumberOfHandGuns,
    [service_plan_header_x].[WeaponLocation],
    [service_plan_header_x].[WeaponOwner],
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Shot_Gun_Rifle]) AS Shot_Gun_Rifle,
    [service_plan_header_x].[Howmany] AS NumberOfRifles,
    [service_plan_header_x].[locationofweapon] AS RifleLocation,
    [service_plan_header_x].[ShotgunOwner],
    [service_plan_header_x].[OtherWeapons],
    [service_plan_header_x].[OtherLocation],
    [service_plan_header_x].[otherweaponowner],
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Otherfamilyweapons]) AS OtherFamilyWeapons,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Child_Visit]) AS Child_Visit,
    CASE WHEN [service_plan_header_x].[Weaponunloaded] = 'True' THEN 'Yes' ELSE 'No' END AS [Weaponunloaded],
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Peeraccessweapon]) AS Peeraccessweapon,
    [service_plan_header_x].[OtherHarmful_Items],
    CASE WHEN [service_plan_header_x].[AmmoSeparate] = 'True' THEN 'Yes' ELSE 'No' END AS [AmmoSeperate],
    CASE WHEN [service_plan_header_x].[WeaponLocked] = 'True' THEN 'Yes' ELSE 'No' END AS [WeaponLocked],
    CASE WHEN [service_plan_header_x].[WeaponDoubleLocked] = 'True' THEN 'Yes' ELSE 'No' END AS [WeaponsDoubleLocked],
    [service_plan_header_x].[Accesstoweapons],
    [service_plan_header_x].[Type_Of_Locks],
    [service_plan_header_x].[LocationKey],
    [service_plan_header_x].[Precautionsforotheritems] AS OtherPrecautions,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[SafetyPrecautions1]) AS SafetyPrecautions1,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Safetyprecautions2]) AS SafetyPrecautions2,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Sleepingarrangments]) AS Sleepingarrangements,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Monitordrugandalcohol]) AS Monitordrugalcohol,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].medicationcompliance) AS medicationcompliance,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[safetysweeps]) AS safetysweeps,
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[sweepfrequency]) AS sweepfrequency,
    [service_plan_header_x].[respite1] AS Respite1Name,
    [service_plan_header_x].[Respite1address],
    [service_plan_header_x].[respite1phone],
    [service_plan_header_x].[Availabilityofrespite1] AS Respite1Availability,
    [service_plan_header_x].[Wheretosupervise] AS Respite1Supervise,
    [service_plan_header_x].[respiteName2],
    [service_plan_header_x].[RespiteAddress2],
    [service_plan_header_x].[respitephonetwo] AS Respite2Phone,
    [service_plan_header_x].[availability2] AS Respite2Availability,
    [service_plan_header_x].[wheretosupervise2] AS Respite2Supervise,
    [service_plan_header_x].[respitename3],
    [service_plan_header_x].[respiteaddress3],
    [service_plan_header_x].[respitephone3],
    [service_plan_header_x].[availability3] AS Respite3Availability,
    [service_plan_header_x].[wheretosupervise3] AS Respite3Supervise,
    [service_plan_header_x].[respite4name],
    [service_plan_header_x].[respite4address],
    [service_plan_header_x].[respite4phone],
    [service_plan_header_x].[availability4] AS Respite4Availablity,
    [service_plan_header_x].[wheretosupervise4] AS Respite4Supervise,
    [service_plan_header_x].[HighRiskIndividuals],
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[safetyplansigned]) AS safetyplansigned,
    --  --Youth Family Review and Consent --Added 3/9/11 - KMH
    CONVERT(varchar(12),[service_plan_header_x].[Date_of_Review],110) as  Date_of_Review , -- Added By lalitha
    CASE WHEN [service_plan_header_x].[reviewoftreatmentgoals] = 'True' THEN 'Yes' ELSE 'No' END AS reviewoftreatmentgoals,
    CASE WHEN [service_plan_header_x].[Safetyinhome]= 'True' THEN 'Yes' ELSE 'No' END AS Safetyinhome,
    CASE WHEN [service_plan_header_x].[Sexuallyinappropriatebx]= 'True' THEN 'Yes' ELSE 'No' END AS Sexuallyinappropriatebx,
    CASE WHEN [service_plan_header_x].[ViolentCrime]= 'True' THEN 'Yes' ELSE 'No' END AS ViolentCrime,
    CASE WHEN [dbo].[fn_GetLUTValue] ([service_plan_header_x].[YouthReviewed])= 'True' THEN 'Yes' ELSE 'No' END AS YouthReviewed,
    CASE WHEN [service_plan_header_x].[CopyProvided] = 'True' THEN 'Yes' ELSE 'No' END AS YouthCopyProvided,
    [service_plan_header_x].[Youth_Comments],
    [dbo].[fn_GetLUTValue] ([service_plan_header_x].[Caregiver_Reviewed]) AS CaregiverReviewed,
    CASE WHEN [service_plan_header_x].[CopyProvided2] = 'True' THEN 'Yes' ELSE 'No' END AS CaregiverCopyProvided,
    [service_plan_header_x].[Caregiver_remarks],
    CASE WHEN [service_plan_header_x].[OtherInvolvedAdult]= 'True' THEN 'Yes' ELSE 'No' END AS OtherInvolvedAdult,
    CASE WHEN [service_plan_header_x].[Reviewed] = 'True' THEN 'Yes' ELSE 'No' END AS OtherAdultReviewed,
    CASE WHEN [service_plan_header_x].[CopyGiven] = 'True' THEN 'Yes' ELSE 'No' End AS OtherAdultCopyProvided,
    [service_plan_header_x].[OtherAdultComments],
    CASE WHEN[service_plan_header_x].[FosterParent]= 'True' THEN 'Yes' ELSE 'No' End AS FosterParent,
    CASE WHEN [service_plan_header_x].[Reviewed2] = 'True' THEN 'Yes' ELSE 'No' END AS FosterParentReviewed,
    CASE WHEN [service_plan_header_x].[copyprovided3] = 'True' THEN 'Yes' ELSE 'No' END AS FostParentCopyProvided,
     [service_plan_header_x].[FosterParentsComments],
    CASE WHEN [service_plan_header_x].[DCSCaseworkerName] = 'True' THEN 'Yes' ELSE 'No' END AS DCSCaseworkerName,
    CASE WHEN [service_plan_header_x].[DCSCaseworker]= 'True' THEN 'Yes' ELSE 'No' END AS DCSCaseworker ,
    CASE WHEN [service_plan_header_x].[DCSCopyProvided]= 'True' THEN 'Yes' ELSE 'No' END AS DCSCopyProvided,
    CASE WHEN [service_plan_header_x].[RRMGName]= 'True' THEN 'Yes' ELSE 'No' END AS RRMGName,
    CASE WHEN [service_plan_header_x].[RRMGCopy]= 'True' THEN 'Yes' ELSE 'No' END AS RRMGCopy,
    CASE WHEN [service_plan_header_x].[RRMGReviewed]= 'True' THEN 'Yes' ELSE 'No' END AS RRMGReviewed,
    CASE WHEN[service_plan_header_x].[SignaturesObtained]= 'True' THEN 'Yes' ELSE 'No' END AS SignaturesObtained,
    [service_plan_header_x].[SignaturePage],
    [ppg_pri].[description] [ppg_primary],
    [ppg_con].[description] [ppg_concurrent]
     
    From [#TEMP_EVENT_LOG] 
    JOIN [evolv_cs].[dbo].[service_plan_header]  With (NoLock) ON [#TEMP_EVENT_LOG].[event_log_id] = [service_plan_header].[event_log_id]
    JOIN [evolv_cs].[dbo].[service_plan_setup_header]  With (NoLock) ON [service_plan_header].[service_plan_setup_header_id] = [service_plan_setup_header].[service_plan_setup_header_id]
    JOIN [evolv_cs].[dbo].[service_plan_header_x]  With (NoLock) ON [service_plan_header].[service_plan_header_id] = [service_plan_header_x].[service_plan_header_id]
    JOIN [evolv_cs].[dbo].[client_personal_view]  With (NoLock) ON [client_personal_view].[people_id] = [#TEMP_EVENT_LOG].[people_id]
    JOIN [evolv_cs].[dbo].[staff] With (NoLock) ON [staff].[staff_id] = [#TEMP_EVENT_LOG].[staff_id]
    JOIN [evolv_cs].[dbo].[people] With (NoLock) ON [people].[people_id] = [staff].[people_id]
    JOIN [evolv_cs].[dbo].[service_track_current_view]  With (NoLock) ON [service_track_current_view].[people_id] = [#TEMP_EVENT_LOG].[people_id]
    
    left join [#TEMP_SUPERVISOR_ID] on [#TEMP_SUPERVISOR_ID].[people_id] = [service_track_current_view].[people_id] --or @SupervisorID is Null 
    JOIN [evolv_cs].[dbo].[program_info] With (NoLock) ON [program_info].[program_info_id]= [#TEMP_EVENT_LOG].[program_providing_service]     --- ADDED BY LALITHA FOR PROGRAM DETAILS
    LEFT JOIN [evolv_cs].[dbo].[group_profile] With (NoLock) ON [group_profile].[group_profile_id]=[#TEMP_EVENT_LOG].[site_providing_service]
    LEFT JOIN [evolv_cs].[dbo].[profile_view] [udl_asp] With (NoLock) ON [udl_asp].[group_profile_id] = [service_plan_header_x].[AftercareServiceProvider]
    LEFT JOIN [evolv_cs].[dbo].[user_defined_lut] [udl_tsp] With (NoLock) ON [udl_tsp].[user_defined_lut_id] = [service_plan_header_x].[Target_StepDown_Placement]
    LEFT JOIN [evolv_cs].[dbo].[profile_view] [udl_pds] With (NoLock) ON [udl_pds].[group_profile_id] = [service_plan_header_x].[Post_Discharge_School_Setting]
    LEFT JOIN [evolv_cs].[dbo].[user_defined_lut] [udl_loe] With (NoLock) ON [udl_loe].[user_defined_lut_id] = [service_plan_header_x].[Length_of_Employment]
    LEFT JOIN [evolv_cs].[dbo].[user_defined_lut] [udl_cs] With (NoLock) ON [udl_cs].[user_defined_lut_id] = [service_plan_header_x].[Custody_Status]
    LEFT JOIN [evolv_cs].[dbo].[people_family_members_view] [udl_pdt] With (NoLock) ON [udl_pdt].[family_member_people_id] = [service_plan_header_x].[Person_discharged_to]
    LEFT JOIN [evolv_reports].[dbo].[ppg_history_view] [ppg_pri] With (NoLock) ON [ppg_pri].[people_id] = [#TEMP_EVENT_LOG].[people_id] And [ppg_pri].[is_primary] = 1
    LEFT JOIN [evolv_reports].[dbo].[ppg_history_view] [ppg_con] With (NoLock) ON [ppg_con].[people_id] = [#TEMP_EVENT_LOG].[people_id] And [ppg_con].[is_primary] = 0
    
    Where  
    (@ClientID Is Null OR [service_track_current_view].[people_id]=@ClientID)--change back to id_no if no go
       AND (@StaffID Is Null OR [#TEMP_EVENT_LOG].[staff_id] = @StaffID)
       AND (@FacilityID Is Null OR [#TEMP_EVENT_LOG].[site_providing_service] = @FacilityID)
       AND (@ProgramID Is Null OR [#TEMP_EVENT_LOG].[program_providing_service] = @ProgramID)
    
    

    When I hover over this Sort's properties in Execution Plan, it says:

    Description: Sort the input.
    
    Estimated Operator Cost: 43.6437 (71%)
    
    Estimated Row Size: 35 B
    
    Estimated Subtree Cost: 52.318
    
    Logical Operation: Sort
    
    Memory Fractions: Memory Fractions Input: 0.0246269, Memory Fractions Output: 0.0246269
    
    Order By: Expr1096 Ascending, [evolv_cs].[dbo].[event_log].actual_date Ascending
    
    Output List: [evolv_cs].[dbo].[event_log].event_log_id, [evolv_cs].[dbo].[event_log].actual_date, Expr1096
    
    Physical Operation: Sort
    
    
    
    

     


    Ryan D
    Tuesday, September 13, 2011 8:45 PM

All replies