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:
- Can anything be done to improve the way filtering is handled?
- Is there any harm in specifying ORDER BY even when the data doesn't need to be
returned ordered?
- Any recommendations related to queries with TOP and OFFSET-FETCH?
Answers
- 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.
- 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.
- 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.