Over the past few weeks and months, we’ve featured the ShowPlan operators used by SQL Server to build the query plan. If you’re just getting started with my Showplan series, you can find a list of all my articles here.

Today, it is time to feature the SORT operator. The SORT operator is quite simple, and my intention with this article is to explain the use of this operator, and demonstrate how you can improve the performance of your queries by avoiding the sorting operation.

As we can see by the name of the operator, the SORT operator sorts all rows received by the operator into order. It’s important to you keep in mind that, in some cases, the SORT operation is performed in the temporary database TempDb. Because TempDB is used for all databases within the SQL Server Instance, this can lead to TempDb becoming a bottle-neck and thereby affecting performance.

It is surprising how often developers and DBAs take the SORT operation for granted, even though it is can be expensive in terms of CPU and I/O. You should always pay due attention to this process, and check to make sure that it does not appear in the query plan unless it is necessary.

SORT into Execution Plans

To see the SORT in practice, let’s start with a simple query that uses the table DimCustomer from the Microsoft Sample AdventureWorksDW database.

The following query does a simple SELECT operation, ordering the result by LastName. Because the table is not so large, the Sort will be performed in memory. That means that the data doesn’t need to be written to disk.

For the query above, we have the following execution plan:

Here we can see that the Sort is performed so as to order the data by LastName. Because I didn’t specify the word ASC or DESC, the result will be ordered ascending; which is the default option.

SORT can also be used to remove duplicate rows, in other words, perform a DISTINCT operation. For instance, consider the following query.

From this, we have the following execution plan:

Here the SORT is a “Distinct SORT”. You’ll notice that I didn’t specify anything in an ORDER BY clause; I’m just getting a distinct list of AddessLine1.

SORT in Memory/Disk

It’s important to know that the SORT operation is a very expensive task, and it usually requires a lot of memory. When the query plan is created, SQL Server reserves the memory to perform the SORT in a ‘grant’; but sometimes this sort is written to disk to run in the TempDb database. To understand more about SQL Server memory grant, look at this article.

To see when the SORT operation is being done in TempDb, we can use the SQL Server Profiler to capture an event called ‘Sort Warnings’.

* Update: Just adding more memory is not the only option, and maybe not the easiest. If you want to know more about the sort warning you should read the comments below in this article. Thanks a lot to Holger, Chris and Celko for all comments. F.A.

Many of these warnings are an indication that you need more memory*. If your application requires the use of many disk-based SORT operations, you can also check physical location of the TempDb database so as to be sure that it is using the best storage subsystem available.

How to Avoid SORT operations

The easiest way to avoid a SORT is by creating an Index. As we know, indexes are ordered by the columns so that , if you create an index covering your query, the Query Optimizer identifies this index and uses it to avoid a SORT operation. Let’s look at a sample using the same query used before.

The following command creates an index using the column LastName.

Now, let’s see the execution plan of the same query that we’ve already used.

For this query, we have the following execution plan:

This time, the Query Optimizer has chosen to read the data from the ix index and then uses a Key Lookup to read the other columns to the clustered index. The query optimizer is smart enough to understand that the index is ordered by the column LastName and there is no reason to order the data again.

Another very common usage of a sorting operation is when developers create reports with a lot of options to allow the end-users to choose how to sort the result. In this case, the ORDER BY clause can often be avoided in your query. It is usually better to sort the data within the client application, not the server.

SQL Server also can use the SORT operator for other operations, for instance to be able to use the Merge algorithm, aAnd you can also avoid this by creating the proper index.

That’s all folks, I hope you’ve enjoyed learning about SORT operator, and I’ll see you soon with more “Showplan Operators”.