Estimated Execution Plan SQL Server Sort?

Question

I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything.

Anyone got any ideas how to get this SORT percentage down or remove it all together? Here is the query I'm running.

SELECT Time_ID,
       Site_Type_ID,
       Abandoned_ID,
       WorkType_ID,
       SUM (staging.dbo.measure.ring_time) AS Ring_Time,
       SUM (staging.dbo.measure.hold_time) AS Hold_Time,
       SUM (staging.dbo.measure.talk_time) AS Talk_Time,
       SUM (staging.dbo.measure.acw_time)  AS ACW_Time,
       COUNT(*)                            CallCount
FROM   measure
       INNER JOIN DataMartEnd.dbo.Time_Dim
         ON measure.StartTimeDate BETWEEN Time_Dim.Time_Start AND Time_Dim.Time_End
       INNER JOIN datamartend.dbo.Site_Type_Dim
         ON measure.DBID = Site_Type_Dim.Site_Type_Code
       INNER JOIN datamartend.dbo.Abandoned_Call_Dim
         ON measure.Abandoned = Abandoned_Call_Dim.abandoned_value
       INNER JOIN DataMartEnd.dbo.Work_Type_Dim
         ON measure.DBID = work_type_dim.MIG_Site_ID
            AND Work_Type_Dim.Work_Type_Code = measure.Queue
            AND measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND Work_Type_Dim.DimEffectiveEndDtm
GROUP  BY Abandoned_ID,
          WorkType_ID,
          Site_Type_ID,
          time_id 

Thanks for the help.

Answer 1

Group by Abandoned_ID, WorkType_ID , Site_Type_ID, time_id

One way to implement a group by is to sort the input. Stream Aggregate Showplan Operator:

The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan.

QED.

Further reading: Craig Freedman blog entry on Stream Aggregate and the alternative Hash Aggregate.

Answer 2

Is this database a star schema? If so, what are the indexes on the dimension tables?

If you do a group by on columns that have sorted indexes, the optinizer will replace some or all of the sorting effort with index scans.

If some of the columns in your group by do have indexes, and others don't, try putting the one with the indexes first in the group by.