Take the 2-minute tour ×

When i execute the following query with execution plan.I could see that 'sort' is most costlist task.But i am not using any sort option in my query.Why it is happening?There is a nested loop join task exist before select.Also please let me know about merge join,hash join and nested loop join.

enter image description here

Ename from dbo.Employee e
select AVG(Salary) from dbo.Employee where 
group by DeptId)
share|improve this question
Please post your SQL query –  praveen Jun 3 '12 at 15:22
@praveen the query was posted, just wasn't code formatted. –  Aaron Bertrand Jun 3 '12 at 15:39
Doesn't address your sorting question but a nice overview of this general plan shape is here –  Martin Smith Jun 3 '12 at 16:44
add comment

2 Answers

up vote 3 down vote accepted

In your SQL query your actually sorting data into groups on your column ( Ename) Sort may be a performance bottleneck but you need to find out .If sorting is really causing a performance issue then you can move it to index as suggested by Aaaroon Bertrand .

The reaon for nested join is SQLserver actually executed 2 query to return back your result .

One is the Avg(Salary ) written in the sub query and another is an outer query which is selecting Ename from Employee Table which is done using Clustered index Scan .These 2 queries are then combined to return the desired result . It is basically comparing each row from the sub query to each row from the outer query which satisfies the predicate .

Merge join is only possible when you have index on the joining columns .Merge joins are some times very fast as they are already sorted .

For in depth explanation you can go through the below link Nested join Hash Join Merge Join

share|improve this answer
add comment

It has to sort to determine which rows have a lower salary per TerritoryID - so the sort is because of this partitioning / grouping.

You can get rid of the sort operation with an index such as:

CREATE INDEX d_s ON dbo.Employee(DeptId, Salary);

But you will have to determine whether the gains for this query, in the context of how often it is executed, justifies the additional index - which may impact other parts of your workload. You'll pay for the sorting/grouping at some point.

Also just because the cost within your query is high it does not mean that this is a problem. Is this query slow? Even a Formula 1 race has a "slow" car that finishes in last place.

share|improve this answer
add comment

Your Answer


