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

perform better than letting SQL Server do it. You'll have more control over indexing and ordering. Also,
if you're tuning a stored procedure, which generates the same spool operator for multiple queries, you
would definitely be better off creating the tempdb table once, rather than allowing SQL Server to create
the same table over and over. Another idea is to see if creating a table variable to hold the intermediate
results would help. For smaller result sets, the table variable may be a better option because the data
won't have to be physically saved to disk. This would save on I/O activity. Finally, for queries that must
spool (and you're likely to have some, if not several) make sure you have adequate tempdb space and
also ensure that the tempdb is properly designed from a hardware and performance perspective.

SeekandScanOperators
The next group of operators to discuss are seek and scan operators. These two operators apply to both
clustered and non-clustered indexes. However, only the scan operator applies to tables.

These operators provide the mechanism that SQL Server employs to read data from tables and
indexes. The difference between scans and seeks is that scans return the entire table or index, whereas
seeks return rows using an index and a filter predicate. Consider the following query for the
AdventureWorks database:

SELECT *
FROM Sales.SalesOrderDetail sod

WHERE ProductID = 870

Scan
With a scan, all rows in the SalesOrderDetail table are read. Each one is then evaluated based on the
where clause ''where ProductID 870''. If the evaluation is true then the row is returned. Because scans

examine every row in the table, whether it qualifies or not, the cost is relative to the total number of rows.
Therefore, a scan is effective if the table is small or a large percentage of rows qualify for the evaluation.
Conversely, if the table is large and most of the rows don't qualify, then a scan is not a good option.

=

Seek
In the example, there's an index on the ProductID column. Thus, in many cases a seek operation will be a
better plan. A seek will use the index to go directly to the rows that evaluate to true in the where clause.
A seek only retrieves rows that qualify. Thus, the cost is proportional to the number of qualifying rows
(actually pages) with the total number of pages. A seek is usually more efficient if the WHERE clause is
highly selective. This will effectively eliminate a large portion of the table.

Table Scan
Table Scan is a logical and physical operator. Table Scan operator works against a table. All rows are
evaluated against the WHERE clause (if there is one). Any matching rows will be output. Contrary to
popular rumor, table scans aren't always bad. If the table is small, say less than 64 data pages, SQL
Server can scan the table just as quickly as using an index. Queries that aren't executed very often
may be better off using table scans. The frequently incurred cost of an update, insert, or delete may
ultimately be more expensive than allowing a seldom-run query to utilize a table scan. Also, no matter
how large the table, if the query retrieves a significant percentage (anything above about 25 percent for
SQL Server) of the table, that, too, makes table scans appropriate.