Systems are often built and measured without the appropriate performance goals in mind. When measuring query performance, for example, most designers tend to measure the time that it takes for the query to complete. By default, this is how SQL Server decides to cost query performance. But this might not be the way your users perceive system performance. To users, performance is often measured by the amount of time that passes between pressing the Enter key and getting some data. As a program designer, you can use this to your advantage. For example, you can make your application begin displaying results as soon as the first few rows are returned; if many rows will appear in the result set, you don't have to wait until they are all processed . You can use such approaches to dramatically improve the user 's perception of the system's responsiveness. Even though the time required to get the last row might be about the same with both approaches, the time it takes to get the first row can be differentand the perceived difference can translate into the success or failure of the project.
By default, SQL Server optimizes a query based on the total estimated cost to process the query to completion. Recall from Chapter 3 that if a significant percentage of the rows in a table must be retrieved, it is better to scan the entire table than to use a nonclustered index to drive the retrieval. (A clustered index, of course, would be ideal because the data would be physically ordered already. The discussion here pertains only to the performance tradeoff of scan-and- sort vs. using a nonclustered index.)
Retrieving a page using the nonclustered index requires traversing the B-tree to get the address of a data page or the clustering key and then retrieving that page (by using the RID to directly access it or traversing the clustered index to find the data page) and then traversing the nonclustered B-tree again to get the location information for the next data page and retrieving itand so on. Many data pages are read many times each, so the total number of page accesses can be more than the total number of pages in the table. If your data and the corresponding nonclustered index are not highly selective, SQL Server usually decides not to use that nonclustered index. That is, if the index is not expected to eliminate more than about 90 percent of the pages from consideration, it is typically more efficient to simply scan the table than to do all the extra I/O of reading B-trees for both the nonclustered and clustered indexes as well as the data pages. And by following the index, each data page must frequently be accessed multiple times (once for every row pointed to by the index). Subsequent reads are likely to be from cache, not from physical I/O, but this is still much more costly than simply reading the page once for all the rows it contains (as happens in a scan).
Scanning the table is the strategy SQL Server chooses in many cases, even if a nonclustered index is available that could be used to drive the query, and even if it would eliminate a sort to return the rows based on an ORDER BY clause. A scan strategy can be much less costly in terms of total I/O and time. However, the choice of a full scan is based on SQL Server's estimate of how long it would take the query to complete in its entirety, not how long it would take for the first row to be returned. If an index exists with a key that matches the ORDER BY clause of the query and the index is used to drive the query execution, there is no need to sort the data to match the ORDER BY clause (because it's already ordered that way). The first row is returned faster by SQL Server chasing the index even though the last row returned might take much longer than if the table were simply scanned and the chosen rows sorted.
In more concrete terms, let's say that a query that returns many rows takes 1 minute to complete using the scan-and-sort strategy and 2 minutes using a nonclustered index. With the scan-and-sort strategy, the user doesn't see the first row until all the processing is almost donefor this example, about 1 minute. But with the index strategy, the user sees the first row within a subsecondthe time it takes to do, say, five I/O operations (read two levels of the nonclustered index, two levels of the clustered index, and then the data page). Scan-and-sort is faster in total time, but the nonclustered index is faster in returning the first row.
SQL Server provides a query hint called FAST that lets SQL Server know that having the first n rows returned quickly is more important than the total time, which would be the normal way query plans get costed. Later in this chapter, we'll look at some techniques for speeding up slow queries and discuss when it is appropriate to use the query hint. For now, you should understand the issues of response time (the time needed to get the first row) vs. throughput (the time needed to get all rows) when you think about your performance goals. Typically, highly interactive systems should be designed for best response time, and batch-oriented systems should be designed for best throughput.