Filtering and Sorting Performance Recommendations :: SQL Server 2012

You are hired as a consultant to help address query performance problems in a beer factory running SQL Server 2012. You trace a typical workload submitted to the system and observe very slow query run times. You see a lot of network traffic. You see that many queries return all rows to the client and then the client handles the filtering. Queries that do filter data often manipulate the filtered columns. All queries have ORDER BY clauses, and when you inquire about this, you are told that it's not really needed, but the developers got accustomed to doing so -- just in case. You identify a lot of expensive sort operations. The customer is looking for recommendations to improve performance and asks you the following questions:
  1. Can anything be done to improve the way filtering is handled?
  2. Is there any harm in specifying ORDER BY even when the data doesn't need to be returned ordered?
  3. Any recommendations related to queries with TOP and OFFSET-FETCH?


  1. For one thing, as much filtering as possible should be done in the database. Doing most of the filtering in the client means that you're scanning more data, which increases the stress on the storage subsystem, and also that you cause unnecessary network traffic. When you do filter in the databases, for example by using the WHERE clause, you should use search arguments that increase the likelihood for efficient use of indexes. You should try as much as possible to avoid manipulating the filtered columns.

  2. Adding an ORDER BY clause means that SQL Server needs to guarantee returning the rows in the requested order. If there are no existing indexes to support the ordering requirements, SQL Server will have no choice but to sort the data. Sorting is expensive with large sets. So the general recommendation is to avoid adding ORDER BY clauses to queries when there are no ordering requirements. And when you do need to return the rows in a particular order, consider arranging supporting indexes that can prevent SQL Server from needing to perform expensive sort operations.

  3. The main way to help queries with TOP and OFFSET-FETCH perform well is by arranging indexes to support the ordering elements. This can prevent scanning all data, in addition to sorting.