Is Order By that costly?

[robertfah]

I've got a large query that has many joins and returns roughly 13k records. I've noticed lately that there's a long pause within the program which this SP is executed, so I decided to run it in the Management Environment to see what's up. Here's the SQL:

SELECT Issues.*, c.CustomerName, l.LocationName, l.LocationShortName, r.RoomName,
			poc.FirstName + ' ' + poc.LastName AS [POC], g.Name AS [Assigned Group], u.FirstName + ' ' + u.LastName AS [Created By], 
			u2.FirstName + ' ' + u2.LastName AS [Assigned User], ist.IssueStatus AS [Status], iss.IssueStatus AS [Sub-Status], 
			p.Priority, issueCat.IssueCategory, o.Origin, it.IssueType, csd_p.Product, csd_v.VersionNumber, svt.SupportVisitType,
			u3.FirstName + ' ' + u3.LastName AS [Resolved By], csd_psv.ProjectedSoftwareVersion, poc.OfficePhone, poc.Email, poc.CustomerID,
			poc.LocationID, csd_psv.ProjectedReleaseDate
FROM Issues Issues
		LEFT OUTER JOIN AllPOCs poc ON Issues.POCID = poc.AllPOCID
		LEFT OUTER JOIN Customers c ON poc.CustomerID = c.CustomerID
		LEFT OUTER JOIN Locations l ON l.LocationID = poc.LocationID
		LEFT OUTER JOIN Origins o ON Issues.OriginID = o.OriginID
		LEFT OUTER JOIN Rooms r ON Issues.RoomID = r.RoomID
		LEFT OUTER JOIN Groups g ON Issues.AssignedGroupID = g.GroupID
		LEFT OUTER JOIN Users u ON Issues.CreatedByUserID = u.UserID
		LEFT OUTER JOIN Users u2 ON Issues.AssignedUserID = u2.UserID
		LEFT OUTER JOIN Users u3 ON Issues.ResolvedByUserID = u3.UserID
		LEFT OUTER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 0) ist ON Issues.IssueStatusID = ist.IssueStatusID
		LEFT OUTER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 1) iss ON Issues.IssueSubStatusID = iss.IssueStatusID
		LEFT OUTER JOIN Priorities p ON Issues.PriorityID = p.PriorityID
		LEFT OUTER JOIN SCMaster.dbo.Products csd_p ON Issues.ProductID = csd_p.ProductID
		LEFT OUTER JOIN SCMaster.dbo.Versions csd_v ON Issues.VersionID = csd_v.VersionID
		LEFT OUTER JOIN SCMaster.dbo.ProjectedSoftwareVersions csd_psv ON Issues.ProjectedSoftwareVersionID = csd_psv.ProjectedSoftwareVersionID
		LEFT OUTER JOIN SupportVisitTypes svt ON Issues.SupportVisitTypeID = svt.SupportVisitTypeID
		LEFT OUTER JOIN IssueCategories issueCat ON Issues.IssueCategoryID = issueCat.IssueCategoryID
		LEFT OUTER JOIN IssueTypes it ON Issues.IssueTypeID = it.IssueTypeID
ORDER BY Issues.IssueID DESC

I found this for execution times:

Client Process Time:  234
Total Execution Time: 826
Wait time on Server Replies: 592

But when I comment out the Order By, I get a HUGE difference in Total time and wait time:

Client Process Time:  203
Total Execution Time: 296
Wait time on Server Replies: 93

so is an Order By that costly? I am using the PK of the table but only in descending order (DESC).

 

[gmmastros]

Usually, ordering by a primary key is not costly because the primary key is (usually) clustered, so the data is already physically sorted by the primary key.

Since you are joining with other tables, it is possible that SQL Server is deciding that a different approach is better. This is probably caused by your joins with the other tables being on other columns (not the PK).

Have you tried running this query through Database Tuning Advisor? It's possible that additional indexes may speed up performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cantor001]

Unusually high fragmentation of the clustered index ? i.e. what are the results of running DBCC SHOWCONTIG against the Issues table.

Possible to get a copy of the query plan for the select containing the order by clause ?

have a good one

 

[robertfah]

ggm, cantor -

here's the actual execution plan (or part of it):

I am working on the SHOWCONTIG results...

 

[gmmastros]

Can you post the results of this:

[tt]
sp_helpindex 'Issues'
[/tt]

This will show what indexes you have on that table.

Since you are joining Issues with AllPOCs on the POCID column, it would benefit you to have an index on that column.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Can you post the results of this:


sp_helpindex 'Issues'

PK_Issues clustered, unique, primary key located on PRIMARY IssueID

it appears as if that's the only index.

Here's the results from DBCC SHOWCONTIG:

DBCC SHOWCONTIG scanning 'Issues' table...
Table: 'Issues' (530816953); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 502
- Extents Scanned..............................: 68
- Extent Switches..............................: 186
- Avg. Pages per Extent........................: 7.4
- Scan Density [Best Count:Actual Count].......: 33.69% [63:187]
- Logical Scan Fragmentation ..................: 31.47%
- Extent Scan Fragmentation ...................: 88.24%
- Avg. Bytes Free per Page.....................: 1212.5
- Avg. Page Density (full).....................: 85.02%

 

[cantor001]

One crucial question I forgot to ask:

"What is the purpose in ordering the result set ?"

If you don't need to do it then the problem goes away!

"Possible to get a copy of the query plan for the select containing the order by clause ?"
Sorry, should hqve been clearer, what I am after is the _difference_ between the query plans for both queries so we can pick up what path the optimizer has chosen for the order by.

From what I can see the results of SHOWCONTIG indicate that PK_Issues is a candidate for defragmenting (drop/create offline if you can manage it), but I'm not convinced it's a contributing factor to the performance issue.

If you are interested in avoiding rather than solving the problem one option would be to issue the select without the order by clause and then encase it in another select which will then order the 13k rows.

I am particluarly lazy in this respect, none of my sps order their results - I leave that up to the client.

Other people will have different opinions so don't take my word for it!