SORT in execution plan, but query doesnt have an order by

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » SORT in execution plan, but query doesnt have...

SORT in execution plan, but query doesnt have an order by

Rate Topic

Display Mode

Topic Options

Author

Message

winston Smith

Posted Monday, January 12, 2009 10:42 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 757, Visits: 1,265

Im running a select statement on a table, with no ORDER BY clause, but the execution plan is still showing a distinct sort. I cant see why this is.

query:

SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Fintan'
OR m.LastName LIKE 'Guihen'
go

Indexes:
CREATE INDEX Member1
ON dbo.member(firstname, lastname, region_no)
go

CREATE INDEX Member2
ON dbo.member(lastname, firstname, region_no)
go

Note, there is also a clustered index on member_no.

When i hover over the sort operation in the execution plan, it says its ordering by Member_no ascending.

Unless i ask it to sort, I cant see why it would. any ideas? I have a theory that its because of the OR clause, which only returns distinct rows, but I still dont know why its sorting.

Post #634851

colin.Leversuch-Roberts

Posted Monday, January 12, 2009 1:05 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 3:44 PM
Points: 2,653, Visits: 677

off the top of my head , an OR causes two scans of the table/index so there will have to be some sort of ordering - well that would be my guess. Have a look at io stats to see if you have two scans.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #634992

Grant Fritchey

Posted Monday, January 12, 2009 1:15 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 12,317, Visits: 23,441

Can post the whole execution plan? Save it as .sqlplan file and zip it up.

On a guess, it's performing an aggregation to satisfy the criteria. It might be accessing the data twice to satisfy the OR clause and then doing a MERGE to get the data. If it's doing a MERGE, then it's ordering the data to satisfy the MERGE.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #635001

GSquared

Posted Monday, January 12, 2009 1:24 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 14,133, Visits: 8,525

Grant Fritchey (1/12/2009)

You beat me to it. Was going to ask about a merge. Those have to be sorted.

- GSquared, RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #635010

GilaMonster

Posted Monday, January 12, 2009 1:27 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 32,249, Visits: 24,735

What SQL's probably doing is a seek on index 1 that retrieves all the rows that match on first name, then a seek on index 2 to retrieve all that match on last name. It then unions those two resultsets together.

If it didn't remove duplicates then any rows that matched on both first name and last name would appear twice in the output set. That's quite obviously wrong and hence the duplicates need to be removed. To find duplicates, the resultset needs to be sorted, hence the distint sort.

Gail Shaw
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #635013

« Prev Topic | Next Topic »

Permissions