There is a very common performance problem on SQL Server which the Database warns you about with a ‘Sort Warning’. I bet that all of you who read this article have at least one sort warning entry in your default trace. Yes, it is warning about a problem that will hurt the performance of your queries badly. It goes like this …

“Sort warning operations that do not fit into memory can degrade query and server performance because multiple passes over the data are required to sort the data. It is important to optimize queries by removing them.“

We know about it, and I’ve already written about it here at Simple Talk, and  there is also plenty of information about it on the internet.

Any kind of SORT operation should be avoided if at all possible, but if you are seeing SORT warnings that the operation has had to spill into TempDB, then they should be investigated and fixed. When you are checking a query that is triggering these warnings, have you ever wondered why SQL Server does not ask for more memory to perform an in-progress sort to avoid spilling onto TempDB? If I tell you that it actually does that, would you believe it?

In this article, I’ll show you how to see what is going on behind the scenes and suggest how to fix the problem

All Sorts of Issues with SORT

When talking about the SORT operator, I like to say that a sort will have one of three consequences: bad, dreadful and awful. Let me demonstrate.

The Bad Sort

If you have a beautifully-crafted and clean query, with a sort running entirely in memory, I would say even that is bad: It is bad because you can sometimes avoid the sort operation and no sort is better than one sort. Creating an index would help by making SQL Server read data that is already ordered and thereby avoid the need for a sort. Let’s see a sample.

Here is a script to create a database and a table that we’ll use to test the truth of what I’ve written.

First, we will perform a query that manages to do the SORT operation in memory, and here is our test query to do that: I’ve forced the Query Optimizer to do a sort by neglecting to put an index on ColChar (CREATE INDEX ixColChar ON SortTable(ColChar)) I’ve used a CONVERT trick so as to get more memory grant and avoid the spill through to TempDB:

This is the actual execution plan:

As we can see, the query plan is estimating that the sort operation represents 60% of the entire cost of the query. But, how bad is it? I mean, how much time did it take to execute the sort? How much CPU and disk did it use?

Well, on SQL Server 2014 we have access to the dm_exec_query_profiles DMV with all that information. So, let’s check it out.

I ran the query on exec_query_profiles DMV when the sort query was running (session 57) and, I was able to see that the clustered index scan took only 92ms to run and with 95% of the sort operation completed it took 1.2 sec and used a lot more of CPU.

As you can see, the sort is an expensive operation. If we take out the ‘convert’ trick and create the index on ColChar that I’ve mentioned it would be enough to avoid the sort and improve the query performance.

The Dreadful sort

The “very bad” consequence happens when execution of the sort happens in-disk, it means it is using TempDB to spill data there. Let’s simulate this, and see the performance results.

Here is the same query, but now without the CONVERT trick that I used to get more memory grant and avoid the spill. We will run it without the ixColChar index:

This is the actual execution plan:

As we can see this is showing the warning on the sort operation, which means: “operator used TempDB to spill data during execution”.

Let’s check the results of the DMV exec_query_profiles:

Notice that now the sort operator is much worse and is taking almost 3.1 seconds to run 98% of the sort.

The Awful Sort

Now we will run the same query as before, the query that is using TempDB to spill data. But now I’ve set the TempDB to a very busy and slow disk.

Check what happens when I ran the query:

Results from the DMV:

Exactly, it is taking almost 20 seconds to finish because I/O is very poor, and the response time for the writes was very bad.

Unfortunately this is a very common experience. We should remember to make sure that TempDB is located on as fast as possible a storage system to avoid this nightmare experience, period.

Just in case you are wondering about the disk response time when the query was running, here it is:

This is a very slow response time.

Some Good News and Some Bad News!

As I said in the beginning of the article, SQL Server does ask for more memory for an in-progress sort to avoid spilling to TempDB. But (there is always a ‘but’), it is not for regular sorts. It only works for index sorts.

What I’m saying is that if you try to create an index that internally will do the same sort operation, that index sort will not spill to disk, even if it initially did not ask for the correct memory grant.

Let me show you. Consider the following command:

Here is the actual execution plan:

As you can see, in order to create the index, SQL Server is reading the data from the clustered index and sorting it by ColChar. This is the same thing as the query we tried. The sort operation does not have the warning, and I’m sure it is not using TempDB. I’ve set a sysinternals process monitor to track the write operations, and it is not using TempDB.

What is happening here is that SQL Server is asking for more memory on the fly. And we can prove it just by looking at an obscure extended event called sort_memory_grant_adjustment.

As we can see, it asked for 9968kb (1246 * 8) of extra memory and it was enough to avoid the spill to disk.

We can also check the DMV dm_exec_query_memory_grants to see the granted memory for a query. Here is what it is showing:

This was initially using 68288kb of memory, but at some point it requested the extra 9968kb, totaling 78256kb of granted memory.

The interesting thing here is that it can “steal” from the buffer pool as much memory as it needs, so as to avoid a spill to disk. Of course this comes at a price, since it would not be good to have a huge memory grant for nasty queries.

The whole point of resource governance and memory grants is to ensure that some memory is kept aside for queries that start later. If this was the default option it would have bad consequences, such as having one huge query that uses all the available memory. The code that does the sort is not able to return memory once it has stolen it. That means that a greedy approach that ignores the memory grant could cause later queries to be delayed and/or fail.

I think Microsoft uses an assumption that any large CREATE INDEX operations are more likely to be running on an idle system (e.g., during a maintenance window) and at that time it is more likely to be ok to use all the available memory for CREATE INDEX operations.

In my opinion it would be very useful to have a traceflag, a hint or a parameter to allow me to enable this “memory grant adjustment” for regular queries. Unfortunately, there is not.

Conclusion

There is no such thing as a good SORT operation in a query. They should be avoided where possible. If you are getting SORT warnings, this is a call for action, because on a hard-working server this will lead to slow-running queries that will lead to even more demands on TempDB. Fortunately, SQL Server now has a range of ways of giving you information about what is going on with a query that is doing a SORT and will give details of how and when requests are made for memory.

That´s all folks.