Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution Plan with Sort Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 7:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:06 AM
Points: 4, Visits: 180
I am playing around with and continuing to learn SQL. I am using the AdventureWorks2012 database with the following query to get a list of people's first and last name and job title, with their job title = Sales Representative and ordering by Last Name:

SELECT p.FirstName
,p.LastName
,e.JobTitle
FROM AdventureWorks2012.Person.Person p
JOIN AdventureWorks2012.HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Sales Representative'
ORDER BY p.LastName

I also created an index on each table which my query uses:

USE [AdventureWorks2012]
GO

/****** Object: Index [IX_JobTitle_BusinessEntityID] Script Date: 3/21/2013 8:48:01 PM ******/
CREATE NONCLUSTERED INDEX [IX_JobTitle_BusinessEntityID] ON [HumanResources].[Employee]
(
[JobTitle] ASC,
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--------------------------------
USE [AdventureWorks2012]
GO

/****** Object: Index [IX_LastName_BusinessEntityID_IN_FirstName] Script Date: 3/21/2013 8:48:52 PM ******/
CREATE NONCLUSTERED INDEX [IX_LastName_BusinessEntityID_IN_FirstName] ON [Person].[Person]
(
[BusinessEntityID] ASC,
[LastName] ASC
)
INCLUDE ( [FirstName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

If I run this query without the order by clause or with a column in the Employee table instead of p.LastName, I do not get a sort operator in the execution plan. When ordering by last name, I get a sort operator between the Nested Loops (Inner Join) and Select in the Execution Plan.

The question becomes would there be a way to remove the sort operator and do the sorting in one of the indexes before the Nested Loops operator or by changing my query?

Should I not worry about and this would be the best it could get?

I am not fully sure what I am looking for within the execution plan so any help would be appreciated.

Thank you.
Post #1434113
Posted Thursday, March 21, 2013 10:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 25, Visits: 2,517
Ken,

Since BusinessEntityId is the Clustered Key, the data is sorted in the table is physically stored in its order. And since BusinessEntityId is being used in the join condition, it is expected that the optimizer would use that Clustered key to get the data and then later sort it by the lastname.

Now if you force the query to use the last name index (which is IX_Person_LastName_FirstName_MiddleName in this case), you would expect the results to be read in order of the index (query below).

SELECT p.FirstName ,p.LastName ,e.JobTitle
FROM AdventureWorks2012.Person.Person p WITH (INDEX (IX_Person_LastName_FirstName_MiddleName))
INNER JOIN AdventureWorks2012.HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Sales Representative'
ORDER BY p.LastName

However there is still a sort operator in the plan. This may be because the optimizer decided that going thru the index in an optimal way, and not necessarily sorting the output might be beneficial in the overall query. So it grabs the data as it can and then it sorts in the end to satisfy the order by clause. If you select the properties of the NonClustered Index Scan operator for the above query, you will see that the output is not ordered.

However, if you use Fast 1 option as below, then the scan of the non-clustered index is sorted (look at the properties of the operator; Ordered is set to true.) And thus a sort is avoided in the later stages.

SELECT p.FirstName ,p.LastName ,e.JobTitle
FROM AdventureWorks2012.Person.Person p WITH (INDEX (IX_Person_LastName_FirstName_MiddleName))
INNER JOIN AdventureWorks2012.HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Sales Representative'
ORDER BY p.LastName
OPTION (FAST 1)

Instead of me parroting what the experts say, please read here and here to see the reasons why.

http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/22/did-you-know-forcing-a-nonclustered-index-scan-to-avoid-sorting.aspx

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/09a6060a-1f72-4438-a3b2-209c240ee4d6/

So you can get to avoid the sort operator, but it has its costs and one main advantage (data streaming can begin early; as written in the second link).
Post #1434128
Posted Friday, March 22, 2013 6:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:37 AM
Points: 13,864, Visits: 28,259
I'd have to test it to be sure, but you might be able to create an index with LastName as the leading edge and the other columns as part of the INCLUDE clause. Although, you don't need to add the clustered key to any non-clustered index because it's already going to be there as part of the structure as provided by SQL Server.

I'd be hard pressed to say more without seeing the execution plan myself.


----------------------------------------------------
"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 Query Performance Tuning
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 #1434225
Posted Friday, March 22, 2013 9:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 7:06 AM
Points: 4, Visits: 180
Thanks you Sam. That answered my question and gave me something to look at and test.
Post #1434329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse