Do You Really Need that Order By?

I recently came across a scenario where an application process was not performing correctly on one database but was working fine on others. The process should have been completing in seconds but was taking minutes with no indication of activity. After some investigation, I found that the process was stuck waiting on a SELECT statement to complete. Even worse, it was holding an exclusive lock on a table which was then blocking new information from processing.

One part of the SELECT query that stood out was that it was ordering by a date field. Considering what the process was doing, there was no need to have the data ordered. Generally, it’s better to have the data sorted on the application side instead of SQL Server but in this case not even that was necessary.

If I ran the SELECT statement in SSMS without the ORDER BY, the statement completed in seconds returning tens of thousands of rows. When the ORDER BY was included, the query had ran for 2 minutes and had only pulled back about 100 rows in the results window.

Unfortunately, updating the application to remove the ORDER BY was not an option. I added a few indexes to the table and that was enough to get the process to complete.

Ordering Some Orders

Let’s take a closer look at the impact of ordering data in SQL Server. First, we’ll create a test table:

CREATE TABLE OrderTest
(
	Id int identity(1,1),
	Order_Date datetime not null,
	Order_Info varchar(100) not null
);
GO

We don’t need millions of rows for this example so we’ll load our test table with 10,000 orders:

DECLARE @RecordCount AS INT = 0;

WHILE @RecordCount < 10000
BEGIN
	INSERT INTO OrderTest
	VALUES (GETDATE(), (CONVERT(VARCHAR(40), NEWID())))

	SET @RecordCount += 1;
END

We’ll turn on statistics, make sure to include the actual execution plan, and run two queries together to compare:

SELECT Id, Order_Info
FROM OrderTest
ORDER BY Order_Date;
GO

SELECT Id, Order_Info
FROM OrderTest;
GO

Looking at our Messages tab first, we see logical reads for both queries at 79 but the query with the order by needed a Worktable.

Moving onto the execution plans, the difference jumps out immediately:

SQL Server shows the query cost of the first query including ORDER BY as 91% and the query without the ORDER BY at only 9%. The Sort operation in the query with ORDER BY drove up the query cost.

Eliminate the Unnecessary

In this example, we can see the effects of unnecessary ordering. The more data that’s involved, the more impact you may see. If it’s really needed, order data on the application side if possible so that it’s less of a burden on SQL Server. Under certain conditions, waiting on data to sort can grind SQL Server activity to a halt.

Thanks for reading!