SQL Server Application and Transact-SQL Performance Checklist

Are Cursors Being Used When They Don't Need to Be?

Cursors of any kind slow SQL Server's performance. While is some cases they cannot be avoided, in many cases they can. So if your application is currently using Transact-SQL cursors, see if the code can be rewritten to avoid them.

If you need to perform row-by-row operations, consider using one or more of these options instead of using a cursor:

• Use temp tables
  
   
• Use WHILE loops
  
   
• Use derived tables
  
   
• Use correlated sub-queries
  
   
• Use CASE statements
  
   
• Use multiple queries

Each of these above options can substitute for a cursor, and they all perform much faster.

If you can't avoid using cursors, then at least try to speed them up. Find out how you can speed up cursors at this webpage.

Are UNION and UNION SELECT Properly Used?

Many people don't fully understand how UNION and UNION SELECT work, and because of this, end up wasting a lot of SQL Server's resources unnecessarily. When UNION is used, it performs the equivalent of a SELECT DISTINCT on the results set. In other words, a UNION will combine two similar recordsets, then search for potential duplicate records and eliminate them. If this is your goal, then using UNION is the correct syntax.

But if the two recordsets you want to combine with the UNION will not have duplicate records, then using UNION is a waste of resources, because it will look for duplicates, even if they don't exist. So if you know there are no duplicates in the recordsets you want to combine, then you will want to use UNION ALL, not UNION. UNION ALL combines recordsets, but does not search for duplicates, which reduces the use of SQL Server resources, boosting overall server performance.

Is SELECT DISTINCT Being Used Properly?

I have noticed that some developers automatically add the DISTINCT clause to the SELECT statement, whether or not it is needed. From a performance perspective, the DISTINCT clause should only be used when its special function is needed, which is to eliminate duplicate records from a recordset. This is because the use of the DISTINCT clause requires that the resultset be sorted and duplicates be eliminated, which uses up valuable SQL Server resources. Of course, if you need to do this, then do so. But if you know that the SELECT statement will never return duplicate records, then using the DISTINCT clause is just an unnecessary waste of SQL Server resources.

Is the WHERE Clause Sargable?

The term "sargable" (which is in effect a made-up word) comes from the pseudo-acronym "SARG", which stands for "Search ARGument," which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index to speed completion of the query. If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table or index scan, which may cause the query's performance to suffer.

Non-sargable search arguments in the WHERE clause, such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE",  and "LIKE '%500'" generally  prevents (but not always) the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.

Not every WHERE clause that has a non-sargable expression in it is doomed to a table scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use an index (if one exists) to help access the data quickly.

In many cases, if there is a covering index on the table, which includes all of the columns in the SELECT, JOIN, and WHERE clauses in a query, then the covering index can be used instead of a table scan to return a query's data, even if it has a non-sargable WHERE clause. But keep in mind that covering indexes have their own drawbacks, such as often producing wide indexes that increase disk I/O when they are read.

In some cases, it may be possible to rewrite a non-sargable WHERE clause into one that is sargable. For example, the clause:

WHERE SUBSTRING(firstname,1,1) = 'm'

can be rewritten like this:

WHERE firstname like 'm%'

Both of these WHERE clauses produce the same result, but the first one is non-sargable (it uses a function) and will run slow, while the second one is sargable, and will run much faster.

If you don't know if a particular WHERE clause is sargable or non-sargable, check out the query's execution plan in Query Analyzer. Doing this, you can very quickly see if the query will be using indexes or table scans to return your results.

With some careful analysis, and some clever thought, many non-sargable queries can be written so that they are sargable.