Database Reference
In-Depth Information
When you rerun the query, the performance improvement is fairly radical (see Figure 9-33 and the I/O and time
in the following result).
Table 'SalesOrderHeader'. Scan count 1, logical reads 5
CPU time = 0 ms, elapsed time = 69 ms.
As you can see, the covering index dropped the reads from 689 to 5 and the time from 87 ms to 69 ms. Normally,
this would be enough. Assume for a moment that this query has to be called frequently. Now, every bit of speed you
can wring from it will pay dividends. Knowing that so much of the data in the indexed columns is
null
, you can adjust
the index so that it filters out the
null
values, which aren't used by the index anyway, reducing the size of the tree and
therefore the amount of searching required.
CREATE NONCLUSTERED INDEX IX_Test
ON Sales.SalesOrderHeader(PurchaseOrderNumber,SalesPersonID)
INCLUDE (OrderDate,ShipDate)
WHERE PurchaseOrderNumber IS NOT NULL AND SalesPersonID IS NOT NULL
WITH (DROP_EXISTING = ON);
The final run of the query is visible in the following result and in Figure
9-10
.
Table 'SalesOrderHeader'. Scan count
1,
logical reads 4
CPU time = 0 ms, elapsed time = 55 ms.
Figure 9-10.
Execution plan with a filtered index
Although in terms of sheer numbers reducing the reads from 5 to 4 isn't much, it is a 20 percent reduction in
the I/O cost of the query, and if this query were running hundreds or even thousands of times in a minute, like some
queries do, that 20 percent reduction would be a great payoff indeed. Another visible evidence of the payoff is in the
execution time, which dropped again from 69 ms to 55 ms.
Filtered indexes improve performance in many ways.
•
Improving the efficiency of queries by reducing the size of the index
•
Reducing storage costs by making smaller indexes
•
Cutting down on the costs of index maintenance because of the reduced size
But, everything does come with a cost. You may see issues with parameterized queries not matching the filtered
index, therefore preventing its use. Statistics are not updated based on the filtering criteria, but rather on the entire
table just like a regular index. Like with any of the suggestions in this topic, test in your environment to ensure that
filtered indexes are helpful.
One of the first places suggested for their use is just like the previous example, eliminating
NULL
values from
the index. You can also isolate frequently accessed sets of data with a special index so that the queries against that
data perform much faster. You can use the
WHERE
clause to filter data in a fashion similar to creating an indexed view
(covered in more detail in the “Indexed Views” section) without the data maintenance headaches associated with
indexed views by creating a filtered index that is a covering index, just like the earlier example.
