Databases Reference
In-Depth Information
here. You will have to be able to identify these issues and rewrite them to conform to a search argument
that breaks down into an evaluation of a column by a constant. Typically, these problems result in Index
Scan operations against an index. If coded correctly they will become index seek operations instead.
Most examples you'll find here are beginner mistakes, but still are worth examining.
Table 9-14 lists some typical T-SQL predicates that cause the optimizer to choose suboptimal index
scanning along with rewritten versions that can seek into the indexes.
Table 9-14: Searchable Predicate Performance Issues
Index Scan
Index Seek
WHERE Year(OrderDate)
=
2001 AND
WHERE OrderDate BETWEEN
07/01/2001'AND '07/31/2001'
Month(OrderDate)
=
7
WHERE DateDiff(d, OrderDate, Getdate())
=
WHERE OrderDate
>
=
CONVERT(varchar(10),
DateAdd(d, -1034, GetDate()), 101)
1034
WHERE LEFT([AccountNumber],
11)
WHERE AccountNumber like '10-4030-014%'
=
'10-4030-014'
WHERE CHARINDEX('10-402',
WHERE AccountNumber like '10-40[ˆ2]%'
AccountNumber)
=
0
One of the signs of this easily correctable bottleneck is when you see what looks like a simple
WHERE predicate on an existing index but the execution plan shows an Index Scan Operator. The plan in
Figure 9-11 shows two execution plans for the first example in the table above.
Figure 9-11
Search WWH ::
Custom Search