Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 288

❑ If you decide to add included columns, don't skimp. Add other highly requested, low-size

columns that are needed. Don't tune one set of queries to have another incur the newly added
I/O costs for no benefit.

Ordering Results
You should view ORDER BY statements with the same level of concern as DISTINCT statements. An
ORDER BY statement can create a Sort operation in the query plan if the data is not already in that
order. This can be a trivial thing if those columns are part of an ordered, clustered, or covering index, but
can also increase the level of I/O for the query. You especially don't want Sort operations occurring that
don't get used. Sort operations without indexes use a lot of CPU and memory, so remove gratuitous order
by statements.

Take a simple example of sorting the results of the Sales Reason table by the ModifiedDate field. Then
add a non-clustered index on the field and look at the differences.

--WITHOUT THE INDEX
Select * from Sales.SalesOrderHEaderSalesReason Order BY ModifiedDate
|--Sort(ORDER BY:([ModifiedDate] ASC))
|--Clustered Index Scan(
OBJECT:([PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID]))

--WITH THE INDEX
Select * from Sales.SalesOrderHEaderSalesReason Order BY ModifiedDate
|--Index Scan(OBJECT:([IX_SOHSR_ModifiedDate]), ORDERED FORWARD)

The presence of an index allows the optimizer to take a shortcut and read from the index to line up
the results. Then the ORDERED FORWARD indicates that bookmark lookups are made to resolve the
primary key stored in the leaf of the non-clustered index and pull the remaining columns for the table.
Without an index, the clustered leaf is scanned and then the results are ordered. This is an increase in
subtree cost of .08 for the indexed sort to 1.98 for the non-indexed sort. Where is the cost difference?
Look in the EstimateCPU column to find the cost jump from .03 to 1.87 for the sort operation. Suffice it to
say that sorting by just any column on a table should be examined for validity if you are having CPU or
memory issues.

Handling Indexed Nullable Columns
Living with nullable columns always creates a logic overhead that must be considered when using the
column in a filter or join. You'll encounter two common methods of either testing specifically for the null
value or converting null values with either an ISNULL() or ANSI COALESCE() function. In this section,
you'll see why one-size-fits-all performance recommendations can sometimes give unusual or at least
unexpected results. In the AW database Production.Product table, the color column is nullable. If writing
a query to select all the products that are either black or without color you'll typically see something like
these two:

SELECT ProductId
FROM Production.Product
WHERE Coalesce(Color, N'Black') = N'Black'

SELECT productid
FROM Production.Product