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.

       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,

Thanks for the help.

  • 1
    Can you let us know the result of the following query? pastebin.com/1RWAHFZK Interested to see how many rows are going into the final join. That is a triangular join on a 70,000 row table with a residual predicate. If thousands of rows are going into the join this could easily be very expensive. Commented Apr 1, 2013 at 11:53

2 Answers 2

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.


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

  • Hi, thanks for that. I'm unsure how i would be able to do this within the query?
    – Richard C
    Commented Apr 1, 2013 at 10:22
  • Looking at my execution plan the stream aggregate operator is used after the sort?
    – Richard C
    Commented Apr 1, 2013 at 10:30
  • The streaming aggregate is for your SUM and COUNT. Commented Apr 1, 2013 at 10:31
  • 1
    @RichardC - You can paste the XML up to a site like pastebin.com. Also best to upload the actual plan not the estimated. Commented Apr 1, 2013 at 10:47
  • 1
    Display an Actual Execution Plan. You wait until is finished. Although you have to clear up what is your question. The reason why the plan contains a sort was explained fairly clearly. Commented Apr 1, 2013 at 11:20

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.