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

Answer 1

hmm, flush the cache on that monster first?

http://msdn.microsoft.com/en-us/library/ms174283.aspx

if its truly sorting then that value has to be in there somewhere.

It will return sorted by whatever sql wants(index order etc)...

It def shouldnt be sorting, flush that "specific" cache, try again. 

Check your views?

Answer 2

Thanks Mike, but display execution plan still returns the same message.

I executed:

DBCC FREEPROCCACHE WITH NO_INFOMSGS

and then reran it.  Could I send you the execution plan file for you to examine more closely?

---

Ryan D

Answer 3

im sure you have been doing research but here is a pretty quick good recap

http://sqlserverpedia.com/wiki/Examining_Query_Execution_Plans

have you showplanned all?

also check out adding an index.

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---sort/