SQL Server Sort Warnings

Sort warnings are raised by the SQL server where there is insufficient available memory on the server to carry out sort operations within a query. In such cases, sort operation is divided into multiple steps affecting the overall performance of the query. Ideally, there should not be any sort warnings noticed on your server.

Detecting Sort Warnings:

In case, your SQL server is throwing up sort warnings, it can be detected using SQL profiler. Hash warnings are not enabled by default in the Events selection of SQL profiler therefore you need to select them explicitly. Hash Warning Event is available under Errors and Warning Events selection section. You can also select other errors and warning events as per your application requirements. Make sure you only select the required events, this would be helpful in avoiding the junk and also reduces the effort spent on analyzing the SQL Profiler trace. For further details on monitoring events on SQL Server, go through https://msdn.microsoft.com/en-us/library/ms190378.aspx

Enable the SQL Profiler traces during while running your application, preferably run a load test to stress the server at Peak load. If there are sort warnings thrown on your SQL server, you can notice them on the fly on SQL profiler. After the test is done, stop the SQL trace. In case you have a very large trace file, it is time consuming to scroll down each and every column of the Profiler trace. One quick way is to use “Organize Columns” options in SQL Profiler.

1. Go the File -> Properties Tab of SQL Profiler trace.

2. Click Events Selection Tab in Trace Properties Window and click “Organize Columns”.

3. Select the “Event Class” Column and use “Up” option to Move it up under “Groups” Section.

4. Select the “EventSubClass” Column and use “Up” option to Move it up until it’s first column under “Columns”.

5. Click OK.

This will quickly organize your trace file under different Event Class sections. Now your trace file on SQL profiler window should look like something similar to below screen shot. In a single go, you now know the different types of Events that have occurred on your SQL Server captured on trace file. Over here, you can see there were Deadlocks, hash warnings, missing join predicate, hash warnings etc were found on the SQL profiler trace. It’s rare that your application will have all these problems, for some demonstration I have purposefully made some problems to occur on SQL Server.

In the above trace file, there are 602 sort warnings thrown by the SQL server. Now, it is clear that there queries that are performing sort operation and there is insufficient memory on the SQL server to carry out the operation smoothly.

Sort warnings EventSubClass:

The sort warning on the SQL profiles comes with different data columns, giving you more information on the nature and complexity of the sort warning. One such important data column of sort warning is EventSubClass, which will help us in understanding the complexity of problem. To know more about SQL Server Sort Warning Event Class Data columns go through https://msdn.microsoft.com/en-us/library/ms178041.aspx.

EventSubClass of sort warning gives the intensity of problem caused by the particular sort warning. When there is insufficient memory on the SQL server to execute a particular sort operation within a query, the sort operation will be divided into multiple passes. Each pass is an overhead and would increase the time taken to complete the sort. EventSubClass of a sort warning will help you in determining whether a sort operation which threw a sort warning was executed in single pass or consumed multiple passes.

If there was no sort warning, then the sort operation was finished in 1 single step.

If EventSubClass = 1, then the sort operation was finished within one pass i.e. it was divided into 2 steps.

If EventSubClass = 2, then the sort operation was finished in multiple number of steps.

If you receive a sort warning with EventSubClass = 2, then make sure that you either tune the Sort operation or increase the memory on the SQL server.

Recommendations to resolve Sort warnings:

1. Increase the memory on the SQL Server.

2. Removing the unnecessary rows that being returned by the sort operation.

3. Also, make sure sorting is done only to the required columns.