Sort Warnings are raised by SQL Server when a sort operation cannot be done in memory and must spill to tempdb. That can cause significant performance degradation, so it’s important to identify sort warnings, and prevent them whenever possible.
When you get Sort Warnings?
Whenever a query is executed, or a stored procedure is invoked, the query optimizer tries to reuse an execution plan. If there is no execution plan for the query or procedure in the Cache, a new plan will be generated. During the plan generation, SQL Server chooses the optimal execution plan operators, and estimates how much memory would be needed for the operators which require it (Sort and Hash Match Operators, for instance). Listing 1 shows a query which requires the Sort Operator when it is run, and its query plan is shown in Figure 1. The query uses the standard SQL Server 2012 sample database –AdventureWorks – and the SalesOrderHeader table in the Sales schema (as do all the samples in this article). I executed code samples in the SQL Server 2012 instance because this version provides additional info about sort warnings in the execution plan.
SELECT SalesOrderID, OrderDate, DueDate, ShipDate, TotalDue FROM Sales.SalesOrderHeader WHERE SalesOrderID < 50000 ORDER BY OrderDate;
By looking at the resultant execution plan, shown on Figure 1, you can see that the query plan got a1,568 KB memory grant.
Figure 1 Execution plan with the Sort Operator
The assignment of memory is estimation-based. The optimizer estimates the number and size of rows returned by the query, and grants memory to the Sort Operator. In this case, the query got the memory which should be sufficient to sort 6.342 rows in memory, since that was the estimated number of rows. Since the actual number and size of rows is the same, the whole sort operation can be carried out in memory. If the memory was not large enough to hold all required rows, the sorting operation would be performed by writing and reading to/from the tempdb. This latter case is a very protracted operation, and performance can be dramatically degraded.
Why can the memory granted to the Sort Operator be insufficient for the sorting operation? This is sometimes because there is not enough memory available to SQL Server, but that aspect will not be covered in this article. Usually, the Sort Operator does not get enough memory because SQL Server thinks that the memory granted is sufficient for the sort operation. It may well have more memory available, but it is convinced that the query does not require more than it has been granted. Therefore, good estimation is essential to reduce the potential for Sort Warning problems.
So, the first reason there may be sort warnings is because an incorrect estimate has been made by the optimizer. The problem occurs when the number of rows estimated is significantly less than the actual number of rows, or when the row size is bigger than estimated.
The second situation in which we can expect sort warnings is Parameter Sniffing in stored procedures. SQL Server uses a cached plan whenever a stored procedure is invoked, and this plan could be suboptimal for some parameter combinations, and lead to Sort Warning problems.
Wrong Cardinality Estimation
The SQL Server optimizer does very good job of optimizing. However, there are situations where it cannot make a good estimate, even if appropriate indexes exist and statistics are up-to-date. For queries with predicates that use comparison operators between different columns of the same table, SQL Server cannot make a good estimate. Let’s use theAdventureWorks database again, and try to get all the orders from the SalesOrderHeader table, where DueDate is afterShipDate. Listing 2 shows the query which implements this request.
SELECT * FROM Sales.SalesOrderHeader WHERE DueDate > ShipDate ORDER BY OrderDate;
The results of running this query are shown in Figure 2:
Figure 2 Wrong cardinality estimation – comparison operators between different columns of the same table
We can see that the Sort Operator has a small icon indicating a sort warning problem, and additionally, in the Properties Window, we can see spill level information. Estimated number of rows indicates that the SQL Server expects about 33 percent of rows in the result set. The query actually returns all the rows from the table, and this discrepancy is big enough to cause sort warning problems.
The same problem occurs in a query with a sort operation which uses a local variable. An appropriate query is shown in Listing 3. It returns all rows from the table SalesOrderHeader where OrderDate is after a given date.
DECLARE @OrderDate DATETIME='20010101'; SELECT * FROM Sales.SalesOrderHeader WHERE OrderDate > @OrderDate ORDER BY DueDate;
The execution plan for the query from Listing 3 is identical to the plan in Figure 2. Again, it is an estimation problem. This time, the optimizer cannot make a good estimate because the value of the variable is not known at the compile time. Therefore it must guess the number of rows, and it guesses as it did in the first case – that about 33 percent of rows will be returned. The estimate isn’t a good one, and query execution ends up with sort warnings.
Parameter Sniffing
The second situation in which we can expect sort warnings is the Parameter Sniffing problem in stored procedures. The execution plan has been generated for the first invocation of the stored procedure and for the parameter combination provided in this invocation. The plan is optimal for the parameters used in the first invocation. SQL Server tries to use this plan for every stored procedure invocation. When the execution plan contains the Sort Operator, and the actual number of rows during the stored procedure execution is significantly greater than the estimated number of rows from the plan, the sort warning problem occurs. The initial memory grant will be used for all invocations, and for some of them, the sort operation spills to tempdb. Listing 4 shows a batch with a stored procedure definition, followed by two procedure invocations.
CREATE PROCEDURE dbo.GetSalesOrderHeader (@OrderDate DATETIME)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate> @OrderDate
ORDER BY DueDate;
END
GO
--first invocation (high selectivity)
EXEC dbo.GetSalesOrderHeader '20090101';
GO
--second invocation (low selectivity)
EXEC dbo.GetSalesOrderHeader '20010101';
GO
The execution plan for the stored procedure has been optimized for highly selective parameters, and it will be used for all invocations. The second invocation does not therefore have enough memory to sort rows, because the minimum amount of memory has been granted, since the SQL Server expects only one row in the result set. The execution plan for the second procedure invocation is shown in Figure 3.
Figure 3 Parameter Sniffing causing a Sort Warning problem
Here there is a more drastic difference between the estimated and actual number of rows, or required and assigned memory, and SQL Server had to perform multiple passes to tempdb to get the sorted output!
Identify Sort Warnings in SQL Server 2008
Sort Warnings problems can be painful; it’s therefore necessary to find out if your system is affected by them, and to identify the queries causing them. Whenever the sort operation spills to tempdb, SQL Server raises the ‘Sort Warnings’ event and it takes single – or multiple – passes to tempdb to get the sorted output. This event can be caught by SQL Server Profiler. Unfortunately SQL Server Profiler doesn’t provide information about the query causing the event. We can only find out if single or multiple passes were required for sorting, but the TextData column is not returned for this event, as shown in Figure 4.
Figure 4 Sort Warnings event in an SQL Server Profiler session
Therefore in SQL Server up to version 2008 R2, we need a workaround to get the query causing the Sort Warnings event to be raised. There are a couple of workarounds for capturing missing information on SQL Server Profiler usage. One approach is to save a profiler trace in a table and create a trigger to capture the query from the cache and update the missing TextDatacolumn. Another possibility is to create a server trace to capture (in addition to the Sort Warnings event) all T-SQL and stored procedure batch or statement completed events, then save them to rollover files, import them later into a table, and write queries to find which queries are associated with the Sort Warnings events. Both of these have drawbacks and I would not recommend either of them. For the first approach, we have to save the trace into a server table, in order to include an additional event, since the TextData column is not provided for the Sort Warning Event. Additionally, when we restart the profiler trace, we have to create the trigger from scratch, and since the table is recreated, we can lose all the data. With the second approach, we end up with a lot of data and pressure on the customer workload if we track all queries from the server.
A more consistent and persistent solution came from SolidQ Mentor Herbert Albert. He suggests the Event Notifications approach to identify sort warnings in versions up to the SQL Server 2008/R2 version. In order to capture queries causing sort warnings by using Event Notifications, you have to perform the following steps:
- Create a table to collect sort warnings data
- Create a procedure that fills the table and is started whenever a message enters the queue
- Create the infrastructure for sort warnings Event Notifications (enable service broker, create a queue to receive messages, create a service on the queue that references the event notifications contract, and finally create an event notification)
In Listing 5 you can see the code for creating a table for data collecting and a stored procedure for queue processing.
Listing 5: Collecting Sort Warnings Info From The Queue
In Listing 6 you can see all the steps necessary to create an event notification for sort warnings in the SQL Server 2008/R2 AdventureWorks database.
Listing 6: Create An Event Notification For Sort Warnings
In order to use Event Notifications, we have to ensure that the Service Broker is enabled for a given database. We also need to specify SET TRUSTWORTHY ON, in order to be able to execute queries against dynamic management views.
We first create a queue for collecting events and put the name of the procedure from Listing 5 in the ACTIVATION part, which ensures that the stored procedure is executed whenever a message enters the queue. Then we create a service on the queue that references the Event Notifications contract. Finally, we create an Event Notification for sort warnings for our database, and then the infrastructure is ready.
Creating and configuring our notification service is thus complete. The service is running, and is collecting sort warnings if they occur. Whenever the Sort Warnings event occurs, an entry will be added into the queue. This forces the execution of the stored procedure dbo.process_sort_warning_event. The procedure gets a message from the queue, combines its data with the data from the dynamic management views sys.dm_exec_connections and sys.dm_exec_sql_text, and inserts the retrieved data into the table dbo.sort_warnings. You can query this table and get the sort warnings and the queries which caused them.
The solution with notification events works very well, is not so expensive, survives a restart and doesn’t need an external application to be running. The solution provided here identifies queries at the batch granularity. That means you get the whole batch of statements in which at least one query caused the sort warning. Since sort warnings usually don’t occur frequently, and there are a couple of sort warning queries, this granularity level is acceptable for sort warning identification. You can use the dynamic management view sys.dm_exec_requests instead of sys.dm_exec_connections and capture queries causing sort warnings at the statement level. At the end of the article, you can find the link to the complete script which includes both solutions from the granularity point of view.
Fortunately, we can identify sort warnings easily and more elegantly in SQL Server 2012.
Identify Sort Warnings in SQL Server 2012
We can use Extended Events in SQL Server 2012 to identify Sort Warnings. The latest SQL Server version provides more than 300 additional extended events, and one of them is called “sort_warning”. We can simply capture the query by using an extended event action. In order to capture queries causing sort warnings, you have to perform the following steps:
- Create an Extended Events session
- Start the session
- Query the session target to check sort warnings
Listing 7 shows a sample Extended Event session which captures this event. The session is configured to collect T-SQL queries related to the sort warning event in a specific database, and put them in a file. It is also configured so that the session will start immediately after creation.
Listing 7: Creating the extended event session to identify sort warning.
To identify the queries that fired the sort_warning event, you have to query the Extended Events target for the created session. Listing 8 shows the code you can use.
Listing 8: Query the Extended Event Target to Return Info About The sort_warning
Similarly to the Event Notifications’ approach, Listing 8 identifies queries causing Sort Warnings at the batch granularity. In thesql_text column, it returns a batch when there are one or more queries that cause a Sort Warnings event to be raised. If you want to go deeper, and identify statements rather than the batch, you have to include an additional action (tsql_frame), and combine the results from the session target with the dynamic management view sys.dm_exec_sql_text. At the end of this article you will find the link to the complete script, which includes both solutions from the granularity point of view.
Additionally, you can use a new Extended Events GUI in SQL Server 2012 to create and configure the Extended Events session, and get target information without writing DDL statements and queries.
In SQL Server 2012, we can identify queries more elegantly, so we opt for the Extended Events-based solution. Extended Events are very powerful and affect system performance only minimally, so it’s preferable to use Extended Events for performance troubleshooting. Unfortunately we cannot identify sort warnings by using Extended Events in SQL Server 2008/2008 R2, because the sort_warning event only exists in SQL Server 2012.
Solving Sort Warnings Problems
We have seen what Sort Warnings are, why they happen and how to identify the queries causing them. Now we’ll see how to solve or prevent this problem.
The Simplest Solution
The best solution for the Sort Warning problem is to rewrite the query which caused it in a manner that makes the Sort Operator unnecessary. The simplest ways to do this are to remove the ORDER BY clause from the query, or to use some other column for sorting. No, this is not a joke! I often see queries with columns in the ORDER BY clause which are not part of explicit requirements. When the ORDER BY is used without the TOP clause, this does not represent the business logic. Since it’s only important for presentation, it could be that sorting on some other column is acceptable, or that there is no need to sort at all. Therefore, you can first check with your project or program manager to find out if the sorting is really required, or if you can use alternative columns for sorting. Sometimes developers write the ORDER BY clause and choose a column simply from habit and for no reason. If this is the case, you can rewrite the query and solve the Sort Warning problem.
Let’s now see how to solve this problem when we cannot eliminate the ORDER BY clause.
Parameter Sniffing
When sort warnings are caused by parameter sniffing, the solution is to use the RECOMPILE query hint in the stored procedure definition. The solution for the stored procedure from Listing 4 is shown in Listing 9.
When sort warnings are caused by parameter sniffing, the solution is to use the RECOMPILE query hint in the stored procedure definition. The solution for the stored procedure from Listing 4 is shown in Listing 9.
Listing 9: Query Causing Sort Warning – Parameter Sniffing in Stored Procedures
This option forces the optimizer to regenerate the execution plan for the statement containing the hint, rather than reusing an existing execution plan. The optimizer still sniffs the parameter value, but this process happens whenever a stored procedure is executed, not only for the first invocation. When an execution plan is generated, the optimizer checks the value of the parameter, chooses an optimal execution plan and grants sufficient memory to the appropriate operators. Figure 5 shows the invocations of two stored procedures. The first one calls the original stored procedure from Listing 4, and the second calls the same procedure with OPTION (RECOMPILE) from Listing 9.
Figure 5: OPTION (RECOMPILE) as solution for Parameter Sniffing
It’s clear that the estimated number of rows and memory granted for the second call are correct.
Note: Do not be confused with the percent values in the costs relative to the batch. Figure 5 shows that the first execution plan is about 5 times better than the second one. This comparison is based on an estimate and this estimate is, as we saw, wrong. Therefore the batch comparison is also irrelevant.
Local Variables
The problem with wrong cardinality due to local variables can be solved with the same option. The SQL Server optimizer can make a good estimate of how many rows it can expect when OrderDate is greater than 1.1.2001, but when we use a local variable, its value is unknown at compile time. Basically, what we have here is a timing problem. At that point in time the SQL Server cannot make a good estimate, and simply assumes that 33% of rows will be returned. It creates a plan for an unknown value. If the variable value is known, SQL Server can make a good estimate, exactly as with a literal value. Therefore, all we need is to do is somehow instruct the optimizer to postpone the plan generation until the time when the value is known. That’s exactly what the OPTION (RECOMPILE) does. This hint forces SQL Server optimizer to generate an execution plan at the statement level, rather than at the batch level, and SQL Server doesn’t have any problem with estimating, as Figure 6 clearly shows.
Figure 6: OPTION (RECOMPILE) as solution for sort warnings due to local variables.
You have to be careful when estimated number of rows differs significantly from the actual number of rows. In this example, the estimate without the RECOMPILE query hint was good enough to decide which plan to use (clustered index scan), but it needs to be more accurate for the memory grant. Because of the bad estimate, the Sort Operator got less memory than required, and the sort operation could not be done in memory.
Compare Different Columns
In this case we don’t have problems with cached plans or variables. SQL Server simply cannot make a good estimate when a comparison operator is used against columns of the same table. In Listing 10 we can see a query where SQL Server cannot make a good estimate (The query is the same as the query from Listing 2)
SELECT * FROM Sales.SalesOrderHeader WHERE DueDate > ShipDate ORDER BY OrderDate;
The SQL Server has statistics for both columns, but doesn’t have statistics for when these two columns are compared. If we can prepare appropriate statistics, the optimizer can check them and make a correct estimate. We can do this by adding a computed column with the expression which implements comparison operations from the WHERE clause. Listing 11 shows the code for adding a computed column and appropriate changes in the query.
ALTER TABLE Sales.SalesOrderHeader ADD DueDateMinusShipDate AS DATEDIFF(day, ShipDate, DueDate); GO SELECT * FROM (Sales.SalesOrderHeader) WHERE DATEDIFF(day, ShipDate, DueDate)>0 ORDER BY OrderDate;
Finally Figure 7 shows that adding a computed column solved the estimation problem for the query from Listing 10.
All the scripts used in this article can be downloaded .
We’ve seen in this article what sort warnings are, and why they are a problem in SQL Server. We have explained what’s going on with the SQL Server optimizer when this problem occurs, and finally we have offered solutions to the problem.
Thanks for reading!
Stay tuned for more news on our blog and subscribe to our newsletter if you want to receive our new posts in your mail, get course discounts…
