You might be aware that SQL Server has a utility for looking at execution plans in a graphical format. (Using Query Analyzer, choose the Display Estimated Execution Plan option from the Query menu.) I prefer using the graphical plan for complex queries. But for these simple queries—for which I want to copy a portion of the plan into this article, for example—the text-based plan is useful.
The first detail to note in the STATISTICS IO results from Query 1 is that SQL Server performed four logical reads, meaning that it had to access four pages of data to find the information it needed to return one row of output. The value returned for physical reads indicates how many of those pages SQL Server had to read from disk, but I won't deal with that value right now. Other factors besides the use or non-use of indexes can impact the physical reads. In the STATISTICS PROFILE output, I'm interested only in whether and how SQL Server used an index. An index seek means that SQL Server used the index in a vertical manner, starting from the root and traversing through the levels of the index to find the data it needed. An index scan means that SQL Server used the index in a horizontal manner, scanning all or part of the leaf level of the index.
For Query 1, the relevant output from the plan is
|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[CustomerID])
This line means that SQL Server used a seek operation on the nonclustered index CustomerID to find the relevant row.
Now, let's compare Query 1 with a similar query that doesn't contain a SARG. To run this query, make sure that you stay in the same Query Analyzer window or that the same SET options are on:
-- Query 2
SELECT * FROM orders
WHERE substring(customerID, 1,3) = 'CEN'
This query returns the exact same row of output as Query 1 but at a higher cost. This query needed 21 logical reads, and the plan shows the following line:
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders])
Because the leaf level of a clustered index is the data itself, scanning a clustered index is the same as scanning the table. SQL Server is basically performing a table scan here, reading every page of the table, because it isn't using an index to help find the necessary row. Because the query has no SARG involving the CustomerID column, the optimizer doesn't even consider using an index.
In "Are You in Tune?" I demonstrated that in some cases, you could easily convert certain non-SARG expressions into SARGs; this is one such case. Query 2 is looking for customer ID values that start with the letters CEN. Using a SARG, you can rewrite the query as
-- Query 3
SELECT * FROM orders
WHERE customerID LIKE 'CEN%'
The logical reads and the execution plan are the same for this query as for Query 1.
Prev. page
1
[2]
3
4
next page 
