Query Design Analysis

A database schema may include a number of performance-enhancement features such as indexes, statistics, and stored procedures. But none of these features guarantees good performance if your queries are written badly in the first place.

Query Design Recommendations

When you need to run a query, you can often use many different approaches to get the same data. In many cases, the optimizer generates the same plan, irrespective of the structure of the query. However, in some situations the query structure won’t allow the optimizer to select the best possible processing strategy. It is important that you are aware that this can happen and, should it occur, what you can do to avoid it.

In general, keep the following recommendations in mind to ensure the best performance:

  • Operate on small result sets.
  • Use indexes effectively.
  • Avoid optimizer hints.
  • Use domain and referential integrity.
  • Avoid resource-intensive queries.
  • Reduce the number of network round-trips.
  • Reduce the transaction cost.

Operating on Small Result Sets

To improve the performance of a query, limit the amount of data it operates on, including both columns and rows. Operating on a small result set reduces the amount of resources consumed by a query and increases the effectiveness of indexes. Two of the rules you should follow to limit the data set’s size are as follows:

  • Limit the number of columns in the select list.
  • Use highly selective WHERE clauses to limit the rows returned.

Using Indexes Effectively

It is extremely important to have effective indexes on database tables to improve performance. However, it is equally important to ensure that the queries are designed properly to use these indexes effectively. These are some of the query design rules you should follow to improve the use of indexes:

  • Avoid nonsargable search conditions.
  • Avoid arithmetic operators on the WHERE clause column.
  • Avoid functions on the WHERE clause column.

Avoiding Optimizer Hints

Since it is usually difficult to outsmart the optimizer, the usual recommendation is to avoid optimizer hints. Generally, it is beneficial to let the optimizer determine a cost-effective processing strategy based on the data distribution statistics, indexes, and other factors.

Using Domain and Referential Integrity

Domain and referential integrity help define and enforce valid values for a column, maintaining the integrity of the database. This is done through column/table constraints.

Domain and referential integrity help the SQL Server 2014 optimizer analyze valid data values without physically accessing the data, which reduces query time.

Avoiding Resource-Intensive Queries

These are a few techniques you can use to reduce the footprint of a query:

  • Avoid data type conversion.
  • Use EXISTS over COUNT(*) to verify data existence.
  • Use UNION ALL over UNION.
  • Use indexes for aggregate and sort operations.
  • Avoid local variables in a batch query.
  • Be careful when naming stored procedures.

Reducing the Number of Network Round-Trips

To reduce the overhead of multiple network round-trips, consider the following techniques:

  • Execute multiple queries together.
  • Use SET NOCOUNT.

Reducing the Transaction Cost

Based on the characteristics of a transaction, here are two broad recommendations to reduce the cost of the transaction:

  • Reduce logging overhead.
  • Reduce lock overhead.