SQL Server Optimization Checklist - SQL Server Query Performance Tuning - page 547

Use the Command SET NOCOUNT ON
As a rule, always use the command SET NOCOUNT ON as the first statement in stored procedures, triggers, and other
batch queries. This enables you to avoid the network overhead associated with the return of the number of rows
affected after every execution of a SQL statement. The command SET NOCOUNT is explained in detail in the
“Use SET NOCOUNT” section of Chapter 19.
Explicitly Define the Owner of an Object
As a performance best practice, always qualify a database object with its owner to avoid the runtime cost required
to verify the owner of the object. The performance benefit of explicitly qualifying the owner of a database object is
explained in detail in the “Do Not Allow Implicit Resolution of Objects in Queries” section of Chapter 15.
Avoid Nonsargable Search Conditions
Be vigilant when defining the search conditions in your query. If the search condition on a column used in the WHERE
clause prevents the optimizer from effectively using the index on that column, then the execution cost for the query
will be high in spite of the presence of the correct index. The performance impact of nonsargable search conditions is
explained in detail in the corresponding section of Chapter 18.
Additionally, please be careful when about providing too much flexibility on search capabilities. If you define
an application feature such as “retrieve all products with product name ending in caps,” then you will have queries
scanning the complete table (or the clustered index). As you know, scanning a multimillion-row table will hurt your
database performance. Unless you use an index hint, you won't be able to benefit from the index on that column.
However, using an index hint overrides the decisions of the query optimizer, so it's generally not recommended that
you use index hints either (see Chapter 18 for more information). To understand the performance impact of such a
business rule, consider the following SELECT statement:
SELECT p.*
FROM Production.Product AS p
WHERE p.[Name] LIKE '%Caps';
In Figure 26-4 , you can see that the execution plan used the index on the [Name] column, but it had to perform
a scan instead of a seek. Since an index on a column with character data types (such as CHAR and VARCHAR ) sorts the
data values for the column on the leading-end characters, using a leading % in the LIKE condition doesn't allow a
seek operation into the index. The matching rows may be distributed throughout the index rows, making the index
ineffective for the search condition and thereby hurting the performance of the query.
Figure 26-4. An execution plan showing a clustered index scan caused by a nonsargable LIKE clause